C#中无聚合动态SQL透视表实现问题求助

本人是C#与SQL新手，遇到动态SQL透视表查询难题。现有追踪文档发行信息的数据库表，包含Number（文档编号，NVARCHAR）、Revision（版本，NVARCHAR）、Status（状态，NVARCHAR）、Date（发行日期）列，需保留这些字段类型。需求如下：

• 将数据转换为以日期为动态列头的透视表，记录各文档对应日期的发行版本
• 可选需求：额外添加最新发行版本及最新版本状态列

原数据库表结构：

目标透视表结构：

带额外列的目标结构（可选）：

尝试的代码：

DROP TABLE tbl_1

SELECT Number, Revision, Status INTO tbl_1 FROM documents

DECLARE @SqlQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(Max)

SELECT @PivotColumns = (SELECT DISTINCT [Date]) FROM documents

SET @SqlQuery = N'SELECT [Number], [Revision], [Status]' + @PivotColumns + '
                INTO TEMP
                FROM tbl_1 
                PIVOT(MAX([Revision])
                FOR 
                [Date] IN (' + @PivotColumns + ')) AS Q'

EXEC sp_executesql @SqlQuery

SELECT * FROM TEMP

问题分析 #

原代码核心错误：

1. @PivotColumns 拼接逻辑错误：未给日期列添加引号，也没有用逗号分隔多个日期，导致生成的SQL语法失效
2. 透视后SELECT语句错误：不能直接包含[Revision]和[Status]，PIVOT已将日期转为列，对应值为Revision

解决方案1：基础动态透视表 #

以下代码生成符合目标结构的透视表：

DECLARE @SqlQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
DECLARE @SelectColumns AS NVARCHAR(MAX)

-- 生成带引号的日期列（用于PIVOT的IN子句）
SELECT @PivotColumns = STRING_AGG(QUOTENAME([Date]), ', ')
FROM (SELECT DISTINCT [Date] FROM documents) AS Dates

-- 生成查询用的日期列
SELECT @SelectColumns = STRING_AGG(QUOTENAME([Date]), ', ')
FROM (SELECT DISTINCT [Date] FROM documents) AS Dates

-- 构建动态SQL
SET @SqlQuery = N'
SELECT [Number], ' + @SelectColumns + '
FROM (
    SELECT [Number], [Revision], [Date]
    FROM documents
) AS SourceData
PIVOT(
    MAX([Revision])
    FOR [Date] IN (' + @PivotColumns + ')
) AS PivotResult
ORDER BY [Number]'

-- 执行动态SQL
EXEC sp_executesql @SqlQuery

解决方案2：带最新版本及状态的透视表 #

通过CTE先计算每个文档的最新版本和状态，再结合透视表：

DECLARE @SqlQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
DECLARE @SelectColumns AS NVARCHAR(MAX)

-- 生成带引号的日期列
SELECT @PivotColumns = STRING_AGG(QUOTENAME([Date]), ', ')
FROM (SELECT DISTINCT [Date] FROM documents) AS Dates

SELECT @SelectColumns = STRING_AGG(QUOTENAME([Date]), ', ')
FROM (SELECT DISTINCT [Date] FROM documents) AS Dates

-- 构建动态SQL，CTE获取最新版本信息
SET @SqlQuery = N'
WITH LatestRevisions AS (
    SELECT 
        [Number],
        [Revision] AS [Last Revision],
        [Status] AS [Last Revision Status],
        ROW_NUMBER() OVER (PARTITION BY [Number] ORDER BY CONVERT(DATE, [Date], 101) DESC) AS RN
    FROM documents
)
SELECT 
    lr.[Number],
    lr.[Last Revision],
    lr.[Last Revision Status],
    ' + @SelectColumns + '
FROM LatestRevisions lr
LEFT JOIN (
    SELECT [Number], ' + @SelectColumns + '
    FROM (
        SELECT [Number], [Revision], [Date]
        FROM documents
    ) AS SourceData
    PIVOT(
        MAX([Revision])
        FOR [Date] IN (' + @PivotColumns + ')
    ) AS PivotResult
) p ON lr.[Number] = p.[Number]
WHERE lr.RN = 1
ORDER BY lr.[Number]'

EXEC sp_executesql @SqlQuery

注意事项 #

• 若Date列存储为字符串类型，需用CONVERT(DATE, [Date], 101)确保日期排序正确（101对应MM/DD/YYYY格式）
• STRING_AGG适用于SQL Server 2017及以上版本，低版本需用FOR XML PATH拼接字符串：

  -- 低版本SQL Server拼接@PivotColumns的方式
  SELECT @PivotColumns = STUFF((SELECT DISTINCT ', ' + QUOTENAME([Date]) 
                                FROM documents 
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
  

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