SQL Server中基于年份转置表并创建视图的技术问询

实现思路 #

要完成将Year列的不同年份转为独立列（如Value_2016）的需求，核心是使用**行转列（PIVOT）**操作，同时需要先对原数据进行分组，生成期望视图中的ID（原表每5条数据对应一组新ID）。

具体SQL代码 #

假设原表名为YourTableName，以下是创建目标视图的完整SQL：

CREATE VIEW vw_TransposedYearValues
AS
WITH GroupedData AS (
    SELECT
        -- 生成分组ID，每5条数据为一组对应视图中的ID
        CEILING(ROW_NUMBER() OVER (ORDER BY ID) / 5.0) AS ID,
        Year,
        Value
    FROM YourTableName
)
SELECT
    ID,
    [2016] AS Value_2016,
    [2018] AS Value_2018,
    [2028] AS Value_2028,
    [2038] AS Value_2038,
    [2048] AS Value_2048
FROM GroupedData
PIVOT (
    -- 用MAX聚合，因为每个分组+年份组合仅对应一个Value值
    MAX(Value)
    FOR Year IN ([2016], [2018], [2028], [2038], [2048])
) AS PivotTable;

代码说明 #

• GroupedData CTE：通过ROW_NUMBER() OVER (ORDER BY ID)给原数据按ID排序生成行号，再用CEILING(行号/5.0)将每5条数据分为一组，得到视图所需的ID。
• PIVOT操作：将Year列的不同取值（2016、2018等）转为列，用MAX(Value)提取每个分组对应年份的数值（每个分组+年份唯一对应一个Value，MAX/AVG/MIN结果一致）。
• 列别名：将PIVOT生成的列名重命名为Value_年份格式，匹配期望的视图结构。

动态扩展方案（可选） #

如果后续Year列会新增年份，静态PIVOT需要手动修改SQL。可以用动态SQL自动获取所有年份并生成视图：

DECLARE @Years NVARCHAR(MAX), @Sql NVARCHAR(MAX);

-- 获取所有不重复的年份，拼接为PIVOT需要的格式
SELECT @Years = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT Year FROM YourTableName) AS Y;

-- 拼接动态SQL
SET @Sql = N'
CREATE VIEW vw_TransposedYearValues
AS
WITH GroupedData AS (
    SELECT
        CEILING(ROW_NUMBER() OVER (ORDER BY ID) / 5.0) AS ID,
        Year,
        Value
    FROM YourTableName
)
SELECT
    ID,
    ' + (SELECT STRING_AGG(QUOTENAME(Year) + ' AS Value_' + CAST(Year AS NVARCHAR), ',') FROM (SELECT DISTINCT Year FROM YourTableName) AS Y) + '
FROM GroupedData
PIVOT (
    MAX(Value)
    FOR Year IN (' + @Years + ')
) AS PivotTable;';

-- 执行动态SQL创建视图
EXEC sp_executesql @Sql;

验证结果 #

创建视图后，执行SELECT * FROM vw_TransposedYearValues即可得到你期望的转置结果。

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