SQL Server基于颜色列值实现行转列的技术问询

嘿，我来帮你搞定这个行转列的需求！先回顾下你的场景：

你当前通过以下查询得到了多行的EmployeeID-Color组合：

Select EmployeeID, Color From dbo.EmployeeID E join dbo.Color C on C.EmployeeID = E.EmployeeID

返回结果示例：

EmployeeID	Color
123	Blue
123	Green
123	Yellow
234	Blue
234	Green

接下来分两种常见场景给你解决方案：

情况1：已知所有要透视的颜色值 #

如果你的Color列只有固定的几个可选值（比如示例里的Blue、Green、Yellow），可以直接用PIVOT函数写死列名：

SELECT EmployeeID, [Blue], [Green], [Yellow]
FROM (
    SELECT E.EmployeeID, C.Color
    FROM dbo.EmployeeID E 
    JOIN dbo.Color C ON C.EmployeeID = E.EmployeeID
) AS SourceTable
PIVOT (
    -- 这里用COUNT或MAX都可以：COUNT会标记是否存在（1/NULL），MAX会显示颜色名（NULL表示不存在）
    COUNT(Color)
    FOR Color IN ([Blue], [Green], [Yellow])
) AS PivotTable;

执行后会得到这样的结果：

如果想把NULL替换成0或者空字符串，直接用ISNULL函数包裹列名就行，比如ISNULL([Yellow], 0)。

情况2：颜色值动态变化（无法提前确定） #

如果Color的可选值会随时新增/修改，没法提前写死列名，就用动态SQL自动生成透视语句：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 自动提取所有唯一颜色值，生成带引号的列名字符串
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.Color)
            FROM dbo.Color c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- 拼接完整的透视查询语句
SET @query = 'SELECT EmployeeID, ' + @cols + ' 
            FROM (
                SELECT E.EmployeeID, C.Color
                FROM dbo.EmployeeID E 
                JOIN dbo.Color C ON C.EmployeeID = E.EmployeeID
            ) AS SourceTable
            PIVOT (
                COUNT(Color)
                FOR Color IN (' + @cols + ')
            ) AS PivotTable';

-- 执行动态生成的SQL
EXECUTE(@query);

这段代码会自动识别Color表中所有的颜色值，不管后续新增多少种颜色，都能自动适配生成对应的列。

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