SQL Server无需SUM的多字段PIVOT行转列实现方案咨询

你之前使用MIN聚合的思路是正确的，同一个cat+numbercat组合下只有唯一的number/namevalue时，MIN/MAX聚合只会过滤掉多行空值、取出唯一有效值，不会对原始数据做任何计算修改，完全可以替代你不想使用的SUM。

方案1：条件聚合（最灵活，易扩展） #

比原生PIVOT语法更适合处理多字段行转列，不需要写多层LEFT JOIN，逻辑直观，后续新增分类只需添加对应行即可：

SELECT 
  cat,
  MAX(CASE WHEN numbercat = '1' THEN number END) AS number1,
  MAX(CASE WHEN numbercat = '1' THEN namevalue END) AS Name1,
  MAX(CASE WHEN numbercat = '2' THEN number END) AS number2,
  MAX(CASE WHEN numbercat = '2' THEN namevalue END) AS Name2,
  MAX(CASE WHEN numbercat = '3' THEN number END) AS number3,
  MAX(CASE WHEN numbercat = '3' THEN namevalue END) AS Name3
FROM #input
GROUP BY cat

方案2：基于原生PIVOT逻辑扩展 #

如果习惯使用PIVOT语法，可以先做一次逆透视合并值列，再统一行转列：

SELECT 
  cat,
  [number1], [namevalue1] AS Name1,
  [number2], [namevalue2] AS Name2,
  [number3], [namevalue3] AS Name3
FROM (
  SELECT 
    cat,
    col_type + numbercat AS col_name,
    col_value
  FROM #input
  UNPIVOT (
    col_value FOR col_type IN (number, namevalue)
  ) upvt
) src
PIVOT (
  MIN(col_value)
  FOR col_name IN ([number1], [namevalue1], [number2], [namevalue2], [number3], [namevalue3])
) piv

动态扩展方案（适配不定数量的分类） #

如果numbercat的值是动态变化的，可以用动态SQL自动生成代码，不需要手动修改适配新增分类：

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

SELECT @cols = STRING_AGG(
  'MAX(CASE WHEN numbercat = ''' + numbercat + ''' THEN number END) AS number' + numbercat + ',
  MAX(CASE WHEN numbercat = ''' + numbercat + ''' THEN namevalue END) AS Name' + numbercat,
  ','
) FROM (SELECT DISTINCT numbercat FROM #input) t

SET @sql = 'SELECT cat, ' + @cols + ' FROM #input GROUP BY cat'

EXEC sp_executesql @sql

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