如何创建SQL视图实现跨表数据转置？

一、固定字段的视图实现（适用于字段稳定的场景） #

如果FormControl中的ColumnName字段暂时不会变化，可以直接用静态PIVOT语法创建视图。以下是两种常见场景的实现：

场景1：直接用ColumnName作为转置后的列名 #

假设FormControl中的ColumnName为UserName、UserAge、UserEmail，FormData表包含对应列，视图实现如下：

CREATE VIEW vw_FormDataTransposed
AS
SELECT FormID, [UserName], [UserAge], [UserEmail]
FROM (
    -- 先将FormData的列转成行（UNPIVOT）
    SELECT FormID, ColumnName, ColumnValue
    FROM FormData
    UNPIVOT (
        ColumnValue FOR ColumnName IN ([UserName], [UserAge], [UserEmail])
    ) AS UnpivotedData
) AS Source
PIVOT (
    -- 聚合函数取唯一值（每个FormID+ColumnName对应唯一值）
    MAX(ColumnValue)
    FOR ColumnName IN ([UserName], [UserAge], [UserEmail])
) AS PivotedData;

场景2：用FormControl的DisplayName作为转置后的列名 #

如果需要用更友好的显示名作为表头，可关联FormControl表实现：

CREATE VIEW vw_FormDataTransposedWithDisplay
AS
SELECT FormID, [用户名], [年龄], [邮箱]
FROM (
    SELECT 
        fd.FormID,
        fc.DisplayName,
        -- 匹配ColumnName对应的值，统一转成字符串避免类型冲突
        CASE fc.ColumnName
            WHEN 'UserName' THEN fd.UserName
            WHEN 'UserAge' THEN CAST(fd.UserAge AS VARCHAR(50))
            WHEN 'UserEmail' THEN fd.UserEmail
        END AS ColumnValue
    FROM FormData fd
    CROSS JOIN FormControl fc
) AS Source
PIVOT (
    MAX(ColumnValue)
    FOR DisplayName IN ([用户名], [年龄], [邮箱])
) AS PivotedData;

二、动态字段的解决方案（适用于字段会变化的场景） #

SQL视图的列结构在创建时必须固定，无法直接支持动态列。但可以通过以下两种方式实现近似动态的效果：

方式1：用存储过程返回动态转置结果 #

存储过程可以动态生成SQL语句，自动读取FormControl中的ColumnName并完成转置：

CREATE PROCEDURE sp_GetDynamicTransposedFormData
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ColumnList NVARCHAR(MAX), @SqlScript NVARCHAR(MAX);

    -- 从FormControl中读取所有ColumnName，生成带引号的列名列表
    SELECT @ColumnList = STRING_AGG(QUOTENAME(ColumnName), ', ')
    FROM FormControl;

    -- 构建动态SQL，先UNPIVOT再PIVOT
    SET @SqlScript = N'
        SELECT FormID, ' + @ColumnList + N'
        FROM (
            SELECT FormID, ColumnName, ColumnValue
            FROM FormData
            UNPIVOT (
                ColumnValue FOR ColumnName IN (' + @ColumnList + N')
            ) AS Unpivoted
        ) AS SourceData
        PIVOT (
            MAX(ColumnValue)
            FOR ColumnName IN (' + @ColumnList + N')
        ) AS PivotedData;
    ';

    -- 执行动态SQL
    EXEC sp_executesql @SqlScript;
END;

调用方式：EXEC sp_GetDynamicTransposedFormData;

方式2：自动更新视图的存储过程 #

如果必须使用视图，可以写一个存储过程，在字段变化时自动重建视图：

CREATE PROCEDURE sp_UpdateTransposedView
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ColumnList NVARCHAR(MAX), @SqlScript NVARCHAR(MAX);

    SELECT @ColumnList = STRING_AGG(QUOTENAME(ColumnName), ', ')
    FROM FormControl;

    -- 删除旧视图（如果存在）
    SET @SqlScript = N'IF EXISTS (SELECT * FROM sys.views WHERE name = ''vw_DynamicFormTransposed'') DROP VIEW vw_DynamicFormTransposed;';
    EXEC sp_executesql @SqlScript;

    -- 创建新视图
    SET @SqlScript = N'
        CREATE VIEW vw_DynamicFormTransposed
        AS
        SELECT FormID, ' + @ColumnList + N'
        FROM (
            SELECT FormID, ColumnName, ColumnValue
            FROM FormData
            UNPIVOT (
                ColumnValue FOR ColumnName IN (' + @ColumnList + N')
            ) AS Unpivoted
        ) AS SourceData
        PIVOT (
            MAX(ColumnValue)
            FOR ColumnName IN (' + @ColumnList + N')
        ) AS PivotedData;
    ';

    EXEC sp_executesql @SqlScript;
END;

当FormControl中的ColumnName变化后，执行EXEC sp_UpdateTransposedView;即可更新视图结构。

常见问题说明 #

• PIVOT失败的常见原因：
  1. 未统一列类型：FormData的不同列类型可能不同，UNPIVOT时需转成统一类型（如NVARCHAR(MAX)）。
  2. 未正确处理聚合：PIVOT必须配合聚合函数，若每个FormID+ColumnName对应唯一值，用MAX()或MIN()均可。
  3. 列名未加引号：动态列名必须用QUOTENAME()包裹，避免关键字或特殊字符报错。

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