SQL列转行技术求助：将宽表转换为长表的实现方案

嘿，这个需求是典型的宽表转长表（行转列）场景，我给你两种实用的实现方案，适配不同的数据库环境：

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

这个写法兼容性拉满，不管是MySQL、PostgreSQL、SQL Server还是Oracle都能跑。核心思路是把每个Indicator列单独拆成一行，再合并结果，同时专门处理所有指标为0的情况（比如ID=4）来返回NULL值。

SELECT ID, 'Indicator1' AS Indicators
FROM your_table
WHERE Indicator1 = 1
UNION ALL
SELECT ID, 'Indicator2' AS Indicators
FROM your_table
WHERE Indicator2 = 1
UNION ALL
SELECT ID, 'Indicator3' AS Indicators
FROM your_table
WHERE Indicator3 = 1
UNION ALL
SELECT ID, 'Indicator4' AS Indicators
FROM your_table
WHERE Indicator4 = 1
UNION ALL
-- 处理所有指标都为0的记录，补充NULL行
SELECT ID, NULL AS Indicators
FROM your_table
WHERE Indicator1 = 0 
  AND Indicator2 = 0 
  AND Indicator3 = 0 
  AND Indicator4 = 0
ORDER BY ID;

方法二：横向连接方案（更高效，适合现代数据库） #

如果你的数据库支持横向连接（比如SQL Server的CROSS APPLY，PostgreSQL/MySQL 8.0+的LATERAL JOIN），可以用更简洁高效的写法，避免多次扫描原表：

针对SQL Server的写法： #

SELECT t.ID, i.Indicators
FROM your_table t
CROSS APPLY (
    VALUES 
        ('Indicator1', t.Indicator1),
        ('Indicator2', t.Indicator2),
        ('Indicator3', t.Indicator3),
        ('Indicator4', t.Indicator4)
) i(Indicators, Flag)
WHERE i.Flag = 1
-- 补充无指标的情况
UNION ALL
SELECT ID, NULL AS Indicators
FROM your_table
WHERE Indicator1 = 0 
  AND Indicator2 = 0 
  AND Indicator3 = 0 
  AND Indicator4 = 0
ORDER BY ID;

针对PostgreSQL/MySQL 8.0+的写法： #

SELECT t.ID, i.Indicators
FROM your_table t
LATERAL (
    VALUES 
        ('Indicator1', t.Indicator1),
        ('Indicator2', t.Indicator2),
        ('Indicator3', t.Indicator3),
        ('Indicator4', t.Indicator4)
) i(Indicators, Flag)
WHERE i.Flag = 1
UNION ALL
SELECT ID, NULL AS Indicators
FROM your_table
WHERE Indicator1 = 0 
  AND Indicator2 = 0 
  AND Indicator3 = 0 
  AND Indicator4 = 0
ORDER BY ID;

要是你想更优雅地处理NULL情况，也可以用LEFT JOIN替代最后的UNION ALL分支（以SQL Server为例）：

SELECT t.ID, COALESCE(i.Indicators, NULL) AS Indicators
FROM your_table t
LEFT JOIN (
    SELECT ID, 'Indicator1' AS Indicators FROM your_table WHERE Indicator1=1
    UNION ALL
    SELECT ID, 'Indicator2' AS Indicators FROM your_table WHERE Indicator2=1
    UNION ALL
    SELECT ID, 'Indicator3' AS Indicators FROM your_table WHERE Indicator3=1
    UNION ALL
    SELECT ID, 'Indicator4' AS Indicators FROM your_table WHERE Indicator4=1
) i ON t.ID = i.ID
GROUP BY t.ID, i.Indicators
ORDER BY t.ID;

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