MySQL 数据透视查询将字段名称放入查询结果中

Question

我有一个 MySQL 表，其中包含我希望在查询结果中显示为值的字段名称。

诸如 SELECT * FROM PatientTable 之类的查询的原始结果是:

+--------------------------------------+--------------------------------------+-------------------------+--------------------+----------------+------------------+------------------+------------------+--------------------+| general_preference_id                | preference_id                        | experimental_medication | only_local_patient | accepts_walkin | accepts_handicap | accepts_medicare | accepts_medicaid | accepts_newpatient |+--------------------------------------+--------------------------------------+-------------------------+--------------------+----------------+------------------+------------------+------------------+--------------------+| 0e61628c-a0d1-11e6-8b76-3f0b1a629ff3 | 00f86ce2-a0d1-11e6-8b76-3f0b1a629ff3 |                       0 |                  1 |              0 |                0 |                1 |                0 |                  1 || 62902706-a173-11e6-8b76-3f0b1a629ff3 | 62336a9e-a173-11e6-8b76-3f0b1a629ff3 |                       0 |                  1 |              0 |                0 |                1 |                0 |                  0 || 69c0333e-a0ba-11e6-8b76-3f0b1a629ff3 | 698bd636-a0ba-11e6-8b76-3f0b1a629ff3 |                       0 |                  0 |              0 |                0 |                0 |                0 |                  1 || 70efaa26-a0c2-11e6-8b76-3f0b1a629ff3 | 09dced0c-a0bc-11e6-8b76-3f0b1a629ff3 |                       0 |                  1 |              0 |                0 |                1 |                0 |                  0 || a2661d30-bd6c-11e6-9f6c-76b11f833da1 | e3ed9d28-ac9c-11e6-86fc-3a326ff3d861 |                       1 |                  0 |              1 |                0 |                1 |                0 |                  1 || c162a270-bbb3-11e6-bff9-8f81d6b1f1a6 | 2a903ca6-9a6e-11e6-8b76-3f0b1a629ff3 |                       0 |                  1 |              1 |                1 |                1 |                0 |                  1 |+--------------------------------------+--------------------------------------+-------------------------+--------------------+----------------+------------------+------------------+------------------+--------------------+

我想运行一个查询来提取格式如下的结果，其中字段名称成为类型字段/列中的值:

+--------------------------------------+--------------------------------------+--------------------------------------+| type                                 | data0                                | data1                                |+--------------------------------------+--------------------------------------+--------------------------------------+| general_preference_id                | 0e61628c-a0d1-11e6-8b76-3f0b1a629ff3 | 62902706-a173-11e6-8b76-3f0b1a629ff3 || preference_id                        | 00f86ce2-a0d1-11e6-8b76-3f0b1a629ff3 | 62336a9e-a173-11e6-8b76-3f0b1a629ff3 || experimental_medication              | 0                                    | 0                                    || only_local_patient                   | 1                                    | 1                                    || accepts_walkin                       | 0                                    | 0                                    || accepts_handicap                     | 0                                    | 0                                    || accepts_medicare                     | 1                                    | 1                                    || accepts_medicaid                     | 0                                    | 0                                    || accepts_newpatient                   | 1                                    | 1                                    |+--------------------------------------+--------------------------------------+--------------------------------------+

Answer 1

无论你使用什么前端工具，你都可以实现这个结果集，但是如果你想在Mysql结果集中得到这个结果集，那么你必须通过在mysql中使用游标的复杂查询来实现这个目标。