SQL Server 2019中按员工ID分组无聚合行转列实现方法

我手头有一批包含登录凭证、状态及URL的数据，部分员工对应多个用户ID（uid），部分仅对应一个。需要在SQL Server 2019中按员工ID（empid）分组，将数据转置成指定格式，请问该怎么操作？

原始数据表 #

empid name  desc uid loc status url
100   Smith AA   U1  CA  O      www
100   Smith AA   U2  CA  C      www
100   Smith BB   U3  CA  C      www
100   Smith CC   U4  NY  C      www
101   Adams BB   U5  CA  C      www
101   Adams CC   U6  NY  C      www

期望结果 #

empid name  AA_uid AA_loc AA_status AA_url BB_uid BB_loc BB_status BB_url CC_uid CC_loc CC_status CC_url
100   Smith U1     CA     O         www    U3     CA     C         www    U4     NY     C         www
100   Smith U2     CA     C         www    NULL   NULL   NULL      NULL   NULL   NULL   NULL      NULL
101   Adams NULL   NULL   NULL      NULL   U5     CA     C         www    U6     NY     C         www

解决方案 #

因为同一个员工（empid）下的同一个描述（desc）可能有多条记录，我们需要先给每个empid+desc的分组添加行号，再通过条件聚合来转置数据。

方法1：固定列的条件聚合（适用于desc值固定的情况） #

先给每个分组生成行号，再用条件聚合展开列：

WITH RankedData AS (
    SELECT 
        empid, name, [desc], uid, loc, status, url,
        -- 给每个empid+desc的分组生成行号，区分同组内的多条记录
        ROW_NUMBER() OVER (PARTITION BY empid, [desc] ORDER BY uid) AS rn
    FROM YourTableName -- 替换成你的实际表名
)
SELECT 
    empid,
    name,
    MAX(CASE WHEN [desc] = 'AA' THEN uid END) AS AA_uid,
    MAX(CASE WHEN [desc] = 'AA' THEN loc END) AS AA_loc,
    MAX(CASE WHEN [desc] = 'AA' THEN status END) AS AA_status,
    MAX(CASE WHEN [desc] = 'AA' THEN url END) AS AA_url,
    MAX(CASE WHEN [desc] = 'BB' THEN uid END) AS BB_uid,
    MAX(CASE WHEN [desc] = 'BB' THEN loc END) AS BB_loc,
    MAX(CASE WHEN [desc] = 'BB' THEN status END) AS BB_status,
    MAX(CASE WHEN [desc] = 'BB' THEN url END) AS BB_url,
    MAX(CASE WHEN [desc] = 'CC' THEN uid END) AS CC_uid,
    MAX(CASE WHEN [desc] = 'CC' THEN loc END) AS CC_loc,
    MAX(CASE WHEN [desc] = 'CC' THEN status END) AS CC_status,
    MAX(CASE WHEN [desc] = 'CC' THEN url END) AS CC_url
FROM RankedData
GROUP BY empid, name, rn
ORDER BY empid, rn;

说明

• ROW_NUMBER()用来给每个empid+desc的分组标记行号，确保同一个员工下，不同desc的记录按行号对齐，同一desc的多条记录会分成独立行。
• 用CASE WHEN筛选不同desc的字段，MAX函数用来提取对应行的有效值（同一分组内对应desc的字段只有一个有效值，MIN也可以达到同样效果）。

方法2：动态SQL（适用于desc值不固定的情况） #

如果desc的取值不确定，或者未来可能新增，可以用动态SQL自动生成所有需要的列：

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

-- 生成所有desc对应的列定义（uid、loc、status、url）
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN [desc] = ''', [desc], ''' THEN uid END) AS ', QUOTENAME([desc] + '_uid'), ',',
        'MAX(CASE WHEN [desc] = ''', [desc], ''' THEN loc END) AS ', QUOTENAME([desc] + '_loc'), ',',
        'MAX(CASE WHEN [desc] = ''', [desc], ''' THEN status END) AS ', QUOTENAME([desc] + '_status'), ',',
        'MAX(CASE WHEN [desc] = ''', [desc], ''' THEN url END) AS ', QUOTENAME([desc] + '_url')
    ),
    ','
) FROM (SELECT DISTINCT [desc] FROM YourTableName) AS t;

-- 构建完整的SQL语句
SET @sql = N'
WITH RankedData AS (
    SELECT 
        empid, name, [desc], uid, loc, status, url,
        ROW_NUMBER() OVER (PARTITION BY empid, [desc] ORDER BY uid) AS rn
    FROM YourTableName
)
SELECT empid, name, ' + @cols + '
FROM RankedData
GROUP BY empid, name, rn
ORDER BY empid, rn;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

说明

• STRING_AGG是SQL Server 2017及以上支持的函数，用来拼接动态列的SQL片段。
• 动态SQL会自动根据表中所有不同的desc值生成对应的列，无需手动修改代码。

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