SQL Server中行转列实现？解决PIVOT列标题异常问题

你遇到的问题很典型——直接用ApprCode和ApprName做PIVOT的列名，自然会把这些字段的具体值（比如X001、James）当成列标题。要得到你想要的带序号后缀的统一列名，咱们得换个思路：先给每个SrNo下的审批记录分配一个递增序号，再基于这个序号来构建动态PIVOT的列。

完整解决方案代码 #

-- 生成测试数据（你已有的代码）
CREATE TABLE #Temp (
 SrNo NVARCHAR(200),
 ApprCode NVARCHAR(200),
 ApprName NVARCHAR(200),
 ApprDate Date
)
INSERT INTO #Temp VALUES ('SR_176','X001','James', '2019-10-03')
INSERT INTO #Temp VALUES ('SR_176','X002','Sam', '2019-10-03')

-- 动态PIVOT实现行转列
DECLARE @sql NVARCHAR(MAX)
DECLARE @pivotColumns NVARCHAR(MAX)

-- 第一步：生成带序号的CTE，给每个SrNo下的记录分配序号
-- 这里用ROW_NUMBER()按ApprDate排序，你可以根据实际需求调整排序字段
WITH NumberedRecords AS (
    SELECT 
        SrNo,
        ApprCode,
        ApprName,
        ApprDate,
        ROW_NUMBER() OVER(PARTITION BY SrNo ORDER BY ApprDate) AS RowNum
    FROM #Temp
)
-- 第二步：拼接需要PIVOT的列（ApprCode_1, ApprName_1, ApprDate_1, ApprCode_2...）
SELECT @pivotColumns = STRING_AGG(
    QUOTENAME('ApprCode_' + CAST(RowNum AS NVARCHAR)) + ', ' +
    QUOTENAME('ApprName_' + CAST(RowNum AS NVARCHAR)) + ', ' +
    QUOTENAME('ApprDate_' + CAST(RowNum AS NVARCHAR)),
    ', '
)
FROM (SELECT DISTINCT RowNum FROM NumberedRecords) AS r

-- 第三步：构建动态SQL语句
SET @sql = N'
WITH NumberedRecords AS (
    SELECT 
        SrNo,
        ApprCode,
        ApprName,
        ApprDate,
        ROW_NUMBER() OVER(PARTITION BY SrNo ORDER BY ApprDate) AS RowNum
    FROM #Temp
)
SELECT SrNo, ' + @pivotColumns + N'
FROM (
    SELECT 
        SrNo,
        CONCAT(''ApprCode_'', RowNum) AS ColName,
        CAST(ApprCode AS NVARCHAR(200)) AS ColValue
    FROM NumberedRecords
    UNION ALL
    SELECT 
        SrNo,
        CONCAT(''ApprName_'', RowNum) AS ColName,
        ApprName AS ColValue
    FROM NumberedRecords
    UNION ALL
    SELECT 
        SrNo,
        CONCAT(''ApprDate_'', RowNum) AS ColName,
        CAST(ApprDate AS NVARCHAR(20)) AS ColValue
    FROM NumberedRecords
) AS SourceData
PIVOT (
    MAX(ColValue)
    FOR ColName IN (' + @pivotColumns + N')
) AS PivotResult'

-- 执行动态SQL
EXEC sp_executesql @sql

-- 清理临时表
DROP TABLE #Temp

代码解释 #

1. NumberedRecords CTE：用ROW_NUMBER()函数给每个SrNo分组下的记录分配唯一序号（比如1、2），这是实现按序号生成列名的核心。
2. 拼接Pivot列：通过STRING_AGG（SQL Server 2017+支持）把每个序号对应的三个字段（ApprCode、ApprName、ApprDate）拼接成带序号后缀的列名。如果你的SQL Server版本低于2017，可以用FOR XML PATH的方式替代STRING_AGG。
3. UNION ALL整合数据：把三个字段转换成“键值对”格式（ColName是带序号的列名，ColValue是字段值），这样就能用一次PIVOT完成所有字段的行转列。
4. 动态PIVOT：基于拼接好的列名执行PIVOT，最终得到你想要的格式。

执行结果 #

运行上述代码后，会输出你期望的结果：

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