我有下表:
tableA
+-----------+--------+
| tableA_id | code |
+-----------+--------+
| 1 | code A |
| 2 | code B |
| 3 | code A |
| 3 | code C |
| 3 | code B |
| 4 | code A |
| 4 | code C |
| 4 | code B |
| 5 | code A |
| 5 | code C |
| 5 | code B |
+-----------+--------+
我想使用查询来显示代码 A、代码 B、代码 C 作为列标题,然后值将显示 tableA_id 条目是否在代码字段中包含该代码。所以像这样:
+-----------+------------------------------+
| tableA_id | code A | code B | code C |
+-----------+------------------------------+
| 1 | yes | | |
| 2 | | yes | yes |
| 3 | yes | yes | yes |
etc...
你能在 SQL 中做到这一点吗?
最佳答案
使用条件聚合(可在不同方言之间移植):
SELECT tableA_id,
MAX(CASE WHEN code ='code A' THEN 'yes' END) AS "code A",
MAX(CASE WHEN code ='code B' THEN 'yes' END) AS "code B",
MAX(CASE WHEN code ='code C' THEN 'yes' END) AS "code C"
FROM tableA
GROUP BY tableA_id;
SqlFiddleDemo_MySQL
SqlFiddleDemo_Postgresql
输出:
╔════════════╦═════════╦═════════╦════════╗
║ tableA_id ║ code A ║ code B ║ code C ║
╠════════════╬═════════╬═════════╬════════╣
║ 1 ║ yes ║ (null) ║ (null) ║
║ 2 ║ (null) ║ yes ║ (null) ║
║ 3 ║ yes ║ yes ║ yes ║
║ 4 ║ yes ║ yes ║ yes ║
║ 5 ║ yes ║ yes ║ yes ║
╚════════════╩═════════╩═════════╩════════╝
有很多可能性(搜索):
PIVOT -> SQL Server/Oracle
CROSSTAB -> Postgresql
SELF OUTER JOIN -> All
CONDITIONAL AGG -> All
...
关于SQL 将行值转换为列标题,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/34655535/