SQL Server复杂透视查询：非结构化数据转规范行式结构

我在SQL Server中有如下结构的原始表：

需要将其转换为如下扁平化结构的结果：

由于该表会频繁新增行，同一月份可能存在不同数据，且月份范围覆盖2012-2022年，无法固定月份列，需通过SQL实现动态转换。

实现方案 #

核心思路 #

1. 拆分行转列：用UNPIVOT将原始表的状态列（1233SDD、1234SDD等）转换为行记录，统一存储属性名、状态编码和对应值。
2. 属性转固定列：用PIVOT将描述、来源、频率、国家这类固定属性转为列，形成每个状态的基础信息表。
3. 分离月份数据：从拆解后的记录中筛选出年月行，提取月份和对应点数。
4. 关联合并：将状态基础信息与对应月份的点数数据关联，得到最终扁平化结果。

动态SQL代码 #

因为状态列和月份行可能动态新增，使用动态SQL自动识别所有目标列和行：

DECLARE @statusColumns NVARCHAR(MAX), @monthRows NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 1. 获取所有状态列（排除Status列）
SELECT @statusColumns = STRING_AGG(QUOTENAME(name), ',')
FROM sys.columns
WHERE object_id = OBJECT_ID('YourTableName') -- 替换为你的表名
  AND name <> 'Status';

-- 2. 获取所有年月行（匹配英文月份+4位年份的格式）
SELECT @monthRows = STRING_AGG(QUOTENAME(Status), ',')
FROM YourTableName -- 替换为你的表名
WHERE Status LIKE '%[0-9][0-9][0-9][0-9]'
  AND Status NOT IN ('描述（Description）', '来源（Source）', '频率（Frequency）', '国家（Country）');

-- 3. 构建并执行动态SQL
SET @sql = N'
WITH UnpivotedData AS (
    -- 拆解状态列到行
    SELECT 
        Status AS Attribute,
        StatusCode,
        AttributeValue
    FROM YourTableName
    UNPIVOT (
        AttributeValue FOR StatusCode IN (' + @statusColumns + N')
    ) AS up
),
AttributePivot AS (
    -- 将固定属性转为列
    SELECT 
        StatusCode,
        [描述（Description）] AS Description,
        [来源（Source）] AS Source,
        [频率（Frequency）] AS Frequency,
        [国家（Country）] AS Country
    FROM UnpivotedData
    PIVOT (
        MAX(AttributeValue) FOR Attribute IN (
            [描述（Description）], [来源（Source）], [频率（Frequency）], [国家（Country）]
        )
    ) AS p
),
MonthData AS (
    -- 提取年月和点数数据
    SELECT 
        StatusCode,
        Attribute AS MonthYear,
        AttributeValue AS Points
    FROM UnpivotedData
    WHERE Attribute IN (' + @monthRows + N')
)
-- 关联基础信息和月份数据
SELECT 
    ap.StatusCode AS [状态（Status）],
    ap.Description AS [描述（Description）],
    ap.Source AS [来源（Source）],
    ap.Frequency AS [频率（Frequency）],
    ap.Country AS [国家（Country）],
    md.MonthYear AS [年月（Month/Year）],
    md.Points AS [点数（Points）]
FROM AttributePivot ap
JOIN MonthData md ON ap.StatusCode = md.StatusCode
ORDER BY ap.StatusCode, md.MonthYear;
';

EXEC sp_executesql @sql;

注意事项 #

• 替换代码中的YourTableName为实际表名。
• 如果年月格式不是英文月份+4位年份，需要调整WHERE Status LIKE '%[0-9][0-9][0-9][0-9]'的匹配规则。
• 动态SQL会自动识别新增的状态列和月份行，无需手动修改代码。

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