SQL Server 2022固定行数转置数据并填充默认值的实现方案

问题背景 #

有一张存储账户版本信息的临时表#Account，表结构及示例数据如下：

CREATE TABLE #Account 
(
    [Account No] int,
    [User] varchar(50),
    [Version No] tinyint,
    [Version Comment] varchar(280),
    [Date] datetime2(2)
)

INSERT INTO #Account
VALUES (1, 'Admin', 1, 'New Account', '2024-03-01 12:00:00.00'),
       (1, 'User', 2, 'Edit Account - Add name', '2024-03-02 8:00:00.00'),
       (1, 'Admin', 3, 'Edit Account - Fix name', '2024-03-02 11:00:00.00'),
       (2, 'Admin', 1, 'New Account', '2024-03-02 14:00:00.00'),
       (3, 'User', 1, 'New Account', '2024-03-03 8:00:00.00'),
       (3, 'Admin', 2, 'Edit Account - Add website url', '2024-03-03 12:00:00.00')

需要针对特定账户编号，将数据转置为如下结构（以账户3为例）：

具体要求：

• 固定保留20个版本列，存在数据的使用表中值，不存在的填充默认值（可自定义）
• 将原表的行数据转置为列，列名格式为UsernameN、TitleN、CommentN（N为1到20的版本号）

已知这类转换通常由前端处理，但当前必须通过SQL实现。最初尝试的代码如下，但无法填充默认值也未完成转置：

DECLARE @AccountNo int = 3

-- 获取各版本的创建用户
SELECT CONCAT('Username', [Version No]) 'Name',
        [User] 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

UNION ALL

-- 获取各版本的标题
SELECT CONCAT('Title', [Version No]) 'Name',
        CONCAT('Version #', [Version No], ' - ', FORMAT(CONVERT(datetime2(2), [Date] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 'MM/dd/yyyy hh:mm tt')) 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

UNION ALL

-- 获取各版本的备注
SELECT CONCAT('Comment', [Version No]) 'Name',
        [Version Comment] 'Value'
FROM #Account
WHERE [Account No] = @AccountNo

考虑过PIVOT/UNPIVOT、序列关联、CASE表达式等方法，但未找到高效可行的实现方式，使用的是SQL Server 2022，求解决方案。

解决方案 #

可以通过生成固定版本序列结合PIVOT来实现，完整SQL代码如下：

DECLARE @AccountNo int = 3;
DECLARE @DefaultValue varchar(280) = 'Some Default Value'; -- 自定义默认值

-- 生成1-20的版本号序列
WITH VersionSequence AS (
    SELECT 1 AS VersionNo
    UNION ALL
    SELECT VersionNo + 1
    FROM VersionSequence
    WHERE VersionNo < 20
),
-- 关联原表数据，填充默认值
AccountVersions AS (
    SELECT
        vs.VersionNo,
        ISNULL(a.[User], @DefaultValue) AS Username,
        ISNULL(
            CONCAT('Version #', vs.VersionNo, ' - ', FORMAT(CONVERT(datetime2(2), a.[Date] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 'MM/dd/yyyy hh:mm tt')),
            @DefaultValue
        ) AS Title,
        ISNULL(a.[Version Comment], @DefaultValue) AS Comment
    FROM VersionSequence vs
    LEFT JOIN #Account a ON vs.VersionNo = a.[Version No] AND a.[Account No] = @AccountNo
),
-- 转置Username列
PivotUsername AS (
    SELECT *
    FROM AccountVersions
    PIVOT (
        MAX(Username)
        FOR VersionNo IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19], [20])
    ) AS PivotUser
),
-- 转置Title列
PivotTitle AS (
    SELECT *
    FROM AccountVersions
    PIVOT (
        MAX(Title)
        FOR VersionNo IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19], [20])
    ) AS PivotTitle
),
-- 转置Comment列
PivotComment AS (
    SELECT *
    FROM AccountVersions
    PIVOT (
        MAX(Comment)
        FOR VersionNo IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
                          [11], [12], [13], [14], [15], [16], [17], [18], [19], [20])
    ) AS PivotComment
)
-- 合并三个转置结果，重命名列
SELECT
    -- Username列重命名
    pu.[1] AS Username1,
    pu.[2] AS Username2,
    pu.[3] AS Username3,
    pu.[4] AS Username4,
    pu.[5] AS Username5,
    pu.[6] AS Username6,
    pu.[7] AS Username7,
    pu.[8] AS Username8,
    pu.[9] AS Username9,
    pu.[10] AS Username10,
    pu.[11] AS Username11,
    pu.[12] AS Username12,
    pu.[13] AS Username13,
    pu.[14] AS Username14,
    pu.[15] AS Username15,
    pu.[16] AS Username16,
    pu.[17] AS Username17,
    pu.[18] AS Username18,
    pu.[19] AS Username19,
    pu.[20] AS Username20,
    -- Title列重命名
    pt.[1] AS Title1,
    pt.[2] AS Title2,
    pt.[3] AS Title3,
    pt.[4] AS Title4,
    pt.[5] AS Title5,
    pt.[6] AS Title6,
    pt.[7] AS Title7,
    pt.[8] AS Title8,
    pt.[9] AS Title9,
    pt.[10] AS Title10,
    pt.[11] AS Title11,
    pt.[12] AS Title12,
    pt.[13] AS Title13,
    pt.[14] AS Title14,
    pt.[15] AS Title15,
    pt.[16] AS Title16,
    pt.[17] AS Title17,
    pt.[18] AS Title18,
    pt.[19] AS Title19,
    pt.[20] AS Title20,
    -- Comment列重命名
    pc.[1] AS Comment1,
    pc.[2] AS Comment2,
    pc.[3] AS Comment3,
    pc.[4] AS Comment4,
    pc.[5] AS Comment5,
    pc.[6] AS Comment6,
    pc.[7] AS Comment7,
    pc.[8] AS Comment8,
    pc.[9] AS Comment9,
    pc.[10] AS Comment10,
    pc.[11] AS Comment11,
    pc.[12] AS Comment12,
    pc.[13] AS Comment13,
    pc.[14] AS Comment14,
    pc.[15] AS Comment15,
    pc.[16] AS Comment16,
    pc.[17] AS Comment17,
    pc.[18] AS Comment18,
    pc.[19] AS Comment19,
    pc.[20] AS Comment20
FROM PivotUsername pu
CROSS JOIN PivotTitle pt
CROSS JOIN PivotComment pc;

代码说明 #

• VersionSequence：递归生成1到20的版本号，确保所有需要的版本列都被覆盖
• AccountVersions：左关联原表，用ISNULL为不存在的版本填充自定义默认值
• 三个PivotCTE分别对Username、Title、Comment三类字段进行转置
• 最后通过CROSS JOIN合并三个转置结果，并重命名列名以符合需求

执行后即可得到指定的转置结构，缺失的版本会自动填充默认值。

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