SQL Server行转列查询实现方案 支持多动态字段场景

适用场景 #

你当前使用的是实体-属性-值（EAV）的键值对存储结构，需要将行存储的type字段取值转为列名，对应value作为列值，以下是两种适配不同场景的实现方案：

方案1：静态PIVOT（列固定、列数少场景） #

如果提前明确所有需要转出的type取值，直接用原生PIVOT语法实现即可：

SELECT 
    Name, Gender, Address, Phone -- 此处写入所有需要转出的type取值作为列名
FROM 
    (SELECT type, value FROM 你的表名) AS SourceTable
PIVOT
(
    MAX(value) -- 单值场景下用MAX/MIN聚合均可，多值场景可按需调整
    FOR type IN (Name, Gender, Address, Phone) -- 此处和上面列名保持一致
) AS PivotTable;

缺点：列需要手动枚举，type取值多、变化频繁时维护成本很高

方案2：动态PIVOT（列数多、列不确定场景，推荐使用） #

如果type取值多且不确定，可通过动态拼接SQL的方式自动生成列，无需手动枚举所有取值，完美适配你提到的包含大量数据项的需求：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 自动拼接所有type取值为列名字符串，格式为[Type1],[Type2],...
SELECT @columns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(type)
    FROM 你的表名
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接完整的PIVOT查询语句
SET @sql = N'
SELECT ' + @columns + N'
FROM
(
    SELECT type, value FROM 你的表名
) AS Source
PIVOT
(
    MAX(value)
    FOR type IN (' + @columns + N')
) AS PivotResult;';

-- 执行动态SQL得到结果
EXEC sp_executesql @sql;

优势：自动适配所有表内存在的type取值，哪怕有上百个字段也无需修改代码，维护成本极低

补充说明 #

• 如果表中同一个type对应多个value，可根据业务需求调整聚合函数，SQL Server 2017及以上版本可使用STRING_AGG(value, ',')合并多值
• 如果需要存储多组数据（比如多个人的信息），只需在源表中新增分组ID字段（比如user_id），在子查询中带出该字段即可自动按ID分组生成多行结果

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