SQL Server中如何实现无聚合的多列PIVOT转置

你遇到的核心问题是列数不确定的动态转置——静态Pivot因为需要提前指定列名，肯定满足不了需求，必须用动态SQL来自动生成所需的列。我给你一步步拆解可行的解决方案：

第一步：先明确关联逻辑 #

首先把两张表通过Ref关联，得到基础数据集，这是后续转置的基础：

SELECT t1.ID, t1.IdCust, t2.Ref, t2.Code, t2.Price
FROM Table1 t1
JOIN Table2 t2 ON t1.Ref = t2.Ref

第二步：动态生成转置用的CASE语句 #

因为Ref+Code的组合数量不确定，我们需要先从Table2中提取所有唯一的组合，自动生成对应的CASE语句来实现列转置。这里用SQL Server 2017+支持的STRING_AGG拼接字符串（旧版本可以用FOR XML PATH替代，后面会补充）：

DECLARE @caseStatements NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 为每个唯一的Ref+Code组合生成对应的Code列和Price列的CASE语句
SELECT @caseStatements = STRING_AGG(
    'MAX(CASE WHEN t2.Ref = ''' + CAST(Ref AS VARCHAR(10)) + ''' AND t2.Code = ''' + Code + ''' THEN t2.Code END) AS ' + QUOTENAME('Code_' + CAST(Ref AS VARCHAR(10)) + '_' + Code) + ',
    MAX(CASE WHEN t2.Ref = ''' + CAST(Ref AS VARCHAR(10)) + ''' AND t2.Code = ''' + Code + ''' THEN t2.Price END) AS ' + QUOTENAME('Price_' + CAST(Ref AS VARCHAR(10)) + '_' + Code),
    ', '
)
FROM (SELECT DISTINCT Ref, Code FROM Table2) AS UniqueCombos;

第三步：构建并执行动态查询 #

把生成的CASE语句拼接到主查询中，然后执行动态SQL：

SET @query = N'
SELECT 
    t1.ID, 
    t1.IdCust,
    ' + @caseStatements + '
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.Ref = t2.Ref
GROUP BY t1.ID, t1.IdCust
';

-- 执行最终的动态查询
EXEC sp_executesql @query;

输出效果示例 #

针对你提供的测试数据，执行后会自动生成如下格式的结果：

兼容SQL Server旧版本（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH来拼接字符串，替换第二步的代码：

SELECT @caseStatements = STUFF((
    SELECT ', 
    MAX(CASE WHEN t2.Ref = ''' + CAST(Ref AS VARCHAR(10)) + ''' AND t2.Code = ''' + Code + ''' THEN t2.Code END) AS ' + QUOTENAME('Code_' + CAST(Ref AS VARCHAR(10)) + '_' + Code) + ',
    MAX(CASE WHEN t2.Ref = ''' + CAST(Ref AS VARCHAR(10)) + ''' AND t2.Code = ''' + Code + ''' THEN t2.Price END) AS ' + QUOTENAME('Price_' + CAST(Ref AS VARCHAR(10)) + '_' + Code)
    FROM (SELECT DISTINCT Ref, Code FROM Table2) AS UniqueCombos
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

这样不管Table2后续新增多少Ref+Code组合，查询都会自动生成对应的列，完美解决列数不确定的问题。

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