如何无需声明变量实现SQL动态透视表并创建视图？

由于SQL Server的常规视图无法直接包含动态SQL，我们可以通过OPENROWSET执行动态拼接的透视查询，实现无需单独声明分类变量的动态透视，同时可以创建可直接调用的视图。

步骤1：启用Ad Hoc Distributed Queries（若未启用） #

如果你的服务器尚未启用该配置，先执行以下语句：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

步骤2：创建动态透视视图 #

CREATE VIEW dbo.DynamicPivotView
AS
SELECT *
FROM OPENROWSET(
    'SQLNCLI',
    'Server=(local);Trusted_Connection=yes;',
    '
    EXEC sp_executesql N''
        SELECT date_time, '' + 
        (SELECT STUFF(
            (SELECT DISTINCT '','' + QUOTENAME(category)
             FROM dbo.tableName
             FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),
            1, 1, '''')
        ) + ''
        FROM (
            SELECT date_time, end_value, category
            FROM dbo.tableName
        ) x
        PIVOT (
            MAX(end_value)
            FOR category IN ('' + 
            (SELECT STUFF(
                (SELECT DISTINCT '','' + QUOTENAME(category)
                 FROM dbo.tableName
                 FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),
                1, 1, '''')
            ) + '')
        ) p
    ''
    '
) AS DynamicPivotResult

使用视图 #

创建完成后，直接查询视图即可获取动态生成的透视表：

SELECT * FROM dbo.DynamicPivotView

注意事项 #

• 该视图会自动适配dbo.tableName中新增的category值，每次查询时都会重新生成列集合
• 需确保执行账户拥有OPENROWSET的执行权限，以及对dbo.tableName的读写权限
• 若category数量过多，可能会触及SQL Server的列数上限（默认最大列数为1024）

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