SQL实现列标题转行值：提取值为Yes的列头与Group#

嘿，这个需求其实就是典型的宽表转长表（Unpivot）场景，刚好适配你提到的「列数远多于行数」的业务情况，下面给你几个靠谱的SQL实现方案：

先明确数据结构与目标 #

原始表结构 #

+--------+-----------+------------+-----------+
| Group# | Person A  | Person B   | Person C  |
+--------+-----------+------------+-----------+
| 1      | yes       | no         | no        |
| 2      | no        | yes        | yes       |
| 3      | yes       | yes        | yes       |
+--------+-----------+------------+-----------+

期望输出结果 #

+--------+-----------+
| Group# | Person    |
+--------+-----------+
| 1      | Person A  |
| 2      | Person B  |
| 2      | Person C  |
| 3      | Person A  |
| 3      | Person B  |
| 3      | Person C  |
+--------+-----------+

方案1：通用UNION ALL写法（兼容所有数据库） #

逻辑最直观，但如果列非常多的话需要重复编写相似语句，适合列数较少的场景：

SELECT Group#, 'Person A' AS Person
FROM your_table
WHERE "Person A" = 'yes'
UNION ALL
SELECT Group#, 'Person B' AS Person
FROM your_table
WHERE "Person B" = 'yes'
UNION ALL
SELECT Group#, 'Person C' AS Person
FROM your_table
WHERE "Person C" = 'yes'
ORDER BY Group#, Person;

方案2：数据库原生UNPIVOT语法（推荐，适合列多场景） #

SQL Server、Oracle等数据库支持原生的UNPIVOT关键字，写法简洁易维护：

SELECT Group#, Person
FROM your_table
UNPIVOT (
    -- 临时列存储yes/no值
    IsPresent FOR Person IN ("Person A", "Person B", "Person C")
) AS unpvt
-- 过滤出值为yes的行
WHERE IsPresent = 'yes'
ORDER BY Group#, Person;

方案3：用VALUES/CROSS JOIN生成行（PostgreSQL/MySQL 8.0+/SQL Server） #

这种方法无需重复编写多个SELECT，尤其适合列数极多的场景，可读性和维护性更好：

PostgreSQL版本 #

SELECT t.Group#, p.Person
FROM your_table t
CROSS JOIN UNNEST(ARRAY[
    ('Person A', t."Person A"),
    ('Person B', t."Person B"),
    ('Person C', t."Person C")
]) AS p(Person, IsPresent)
WHERE p.IsPresent = 'yes'
ORDER BY t.Group#, p.Person;

MySQL 8.0+版本 #

SELECT t.Group#, p.Person
FROM your_table t
CROSS JOIN (
    SELECT 'Person A' AS Person, t."Person A" AS IsPresent UNION ALL
    SELECT 'Person B' AS Person, t."Person B" AS IsPresent UNION ALL
    SELECT 'Person C' AS Person, t."Person C" AS IsPresent
) p
WHERE p.IsPresent = 'yes'
ORDER BY t.Group#, p.Person;

内容的提问来源于stack exchange，提问作者user9825672