SQL Pivot转置文本时重复值被聚合的替代方案咨询

你当前的写法直接以mis的值作为行转列的键，同一个mis值对应的多条codex记录会被聚合函数max()合并仅保留一条，同时SQL原生不支持结果集出现同名列，所以最终只会保留每个mis对应的1条数据，不符合预期。

核心思路是给pax分组下、相同mis的记录增加唯一序号，将mis和序号拼接为唯一的列标识，保证所有codex都能单独转成一列：

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

-- 生成带序号的唯一列名，保证同mis的多条数据都可单独成列
SELECT @cols = @cols + QUOTENAME(CONCAT(mis,'_',rn)) + ',' 
FROM (
    SELECT DISTINCT mis, rn 
    FROM (
        SELECT mis, ROW_NUMBER() OVER(PARTITION BY pax, mis ORDER BY codex) rn 
        FROM #dd
    ) t
) as tmp
ORDER BY rn, mis -- 列顺序可按需求调整排序规则
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols)) -- 去除末尾多余的逗号

SET @query = 
'SELECT pax, ' + @cols + ' from 
(
    -- 源数据新增序号字段，拼接为唯一的转列键
    SELECT pax, codex, CONCAT(mis,''_'',rn) as mis_key
    FROM (
        SELECT pax, codex, mis,
               ROW_NUMBER() OVER(PARTITION BY pax, mis ORDER BY codex) rn
        FROM #dd
    ) t
) src
PIVOT 
(
    MAX(codex) for mis_key in (' + @cols + ')
) piv'

EXECUTE(@query)

列名适配（可选） #

SQL原生不支持查询结果出现同名列，如果你需要表头显示为你预期的纯数字重复值，可在查询完成后，在应用层把列名按_分割取前半部分即可，也可以导出到Excel等工具后手动修改表头。

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