如何将EXEC sp_executesql的动态执行结果转换为视图或表？

由于SQL Server的视图必须是静态定义，无法随业务动态新增列，所以针对你的需求，推荐以下几种落地方案：

方案1：用存储过程实现动态列查询 #

将原有动态SQL封装为存储过程，每次调用时自动读取最新的测试项并生成对应列，完美适配测试项动态新增的场景。

存储过程代码 #

CREATE PROCEDURE dbo.GetBloodTestPivotResults
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @cols AS NVARCHAR(MAX),
        @sql  AS NVARCHAR(MAX);

    -- 构建动态列名（自动获取所有测试项）
    SET @cols = STUFF(
        (SELECT N',' + QUOTENAME(y)
         FROM (
             SELECT DISTINCT UPPER(TestsPeformed.value) AS y 
             FROM badger.[Mother_DIGN_BloodTestsAndResults]
             CROSS APPLY STRING_SPLIT(TestsPeformed, ',') as TestsPeformed
         ) AS Y
         ORDER BY y
         FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),
        1, 1, N'');

    -- 构建并执行动态PIVOT查询
    SET @sql = N'
        SELECT EntityID, ' + @cols + N'
        FROM (
            SELECT 
                EntityID, 
                UPPER(TestsPeformed.value) as Test
            FROM badger.[Mother_DIGN_BloodTestsAndResults]
            CROSS APPLY STRING_SPLIT(TestsPeformed, '','') as TestsPeformed
            GROUP BY EntityID, TestsPeformed.value
        ) t
        PIVOT (
            COUNT(Test)
            FOR Test IN(' + @cols + N')
        ) AS pvt;';

    EXEC sp_executesql @sql;
END
GO

使用方式 #

直接调用存储过程即可获取最新结果：

EXEC dbo.GetBloodTestPivotResults;

方案2：定时更新的永久表（模拟视图体验） #

如果需要像普通表/视图一样直接查询，可创建永久表，通过SQL Server代理定期执行动态SQL更新表结构和数据，适合对实时性要求不高的场景。

步骤1：创建存储过程负责更新表 #

CREATE PROCEDURE dbo.UpdateBloodTestPivotTable
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @cols AS NVARCHAR(MAX),
        @dropOldCols AS NVARCHAR(MAX),
        @addNewCols AS NVARCHAR(MAX),
        @sql  AS NVARCHAR(MAX);

    -- 获取当前所有测试项
    SET @cols = STUFF(
        (SELECT N',' + QUOTENAME(y)
         FROM (
             SELECT DISTINCT UPPER(TestsPeformed.value) AS y 
             FROM badger.[Mother_DIGN_BloodTestsAndResults]
             CROSS APPLY STRING_SPLIT(TestsPeformed, ',') as TestsPeformed
         ) AS Y
         ORDER BY y
         FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),
        1, 1, N'');

    -- 生成删除旧测试项列的SQL
    SELECT @dropOldCols = COALESCE(@dropOldCols + ', ', '') + 'DROP COLUMN ' + QUOTENAME(name)
    FROM sys.columns 
    WHERE object_id = OBJECT_ID('dbo.BloodTestPivotResults') 
      AND name != 'EntityID';

    -- 生成添加新测试项列的SQL
    SELECT @addNewCols = COALESCE(@addNewCols + ', ', '') + QUOTENAME(y) + ' INT DEFAULT 0'
    FROM (
        SELECT DISTINCT UPPER(TestsPeformed.value) AS y 
        FROM badger.[Mother_DIGN_BloodTestsAndResults]
        CROSS APPLY STRING_SPLIT(TestsPeformed, ',') as TestsPeformed
    ) AS Y;

    -- 先删除旧列
    IF @dropOldCols IS NOT NULL
    BEGIN
        SET @sql = 'ALTER TABLE dbo.BloodTestPivotResults ' + @dropOldCols + ';';
        EXEC sp_executesql @sql;
    END

    -- 添加新列
    IF @addNewCols IS NOT NULL
    BEGIN
        SET @sql = 'ALTER TABLE dbo.BloodTestPivotResults ADD ' + @addNewCols + ';';
        EXEC sp_executesql @sql;
    END

    -- 清空表并插入最新数据
    TRUNCATE TABLE dbo.BloodTestPivotResults;

    SET @sql = N'
        INSERT INTO dbo.BloodTestPivotResults (EntityID, ' + @cols + N')
        SELECT EntityID, ' + @cols + N'
        FROM (
            SELECT 
                EntityID, 
                UPPER(TestsPeformed.value) as Test
            FROM badger.[Mother_DIGN_BloodTestsAndResults]
            CROSS APPLY STRING_SPLIT(TestsPeformed, '','') as TestsPeformed
            GROUP BY EntityID, TestsPeformed.value
        ) t
        PIVOT (
            COUNT(Test)
            FOR Test IN(' + @cols + N')
        ) AS pvt;';

    EXEC sp_executesql @sql;
END
GO

步骤2：初始化永久表 #

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'BloodTestPivotResults')
BEGIN
    CREATE TABLE dbo.BloodTestPivotResults (
        EntityID INT PRIMARY KEY -- 根据实际EntityID的数据类型调整
    );
END
-- 首次执行更新
EXEC dbo.UpdateBloodTestPivotTable;

步骤3：创建定时更新作业 #

通过SQL Server代理创建作业，设置定期执行EXEC dbo.UpdateBloodTestPivotTable;（比如每天凌晨1点），确保表中数据和列始终最新。之后直接查询该表即可：

SELECT * FROM dbo.BloodTestPivotResults;

方案3：临时表（适合单次会话查询） #

如果仅在当前会话中需要使用该结果，可封装存储过程将数据写入临时表，会话结束后临时表自动销毁。

存储过程代码 #

CREATE PROCEDURE dbo.PopulateBloodTestPivotTempTable
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @cols AS NVARCHAR(MAX),
        @sql  AS NVARCHAR(MAX);

    SET @cols = STUFF(
        (SELECT N',' + QUOTENAME(y)
         FROM (
             SELECT DISTINCT UPPER(TestsPeformed.value) AS y 
             FROM badger.[Mother_DIGN_BloodTestsAndResults]
             CROSS APPLY STRING_SPLIT(TestsPeformed, ',') as TestsPeformed
         ) AS Y
         ORDER BY y
         FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)'),
        1, 1, N'');

    SET @sql = N'
        IF OBJECT_ID(''tempdb..#BloodTestPivot'') IS NOT NULL
            DROP TABLE #BloodTestPivot;

        SELECT EntityID, ' + @cols + N'
        INTO #BloodTestPivot
        FROM (
            SELECT 
                EntityID, 
                UPPER(TestsPeformed.value) as Test
            FROM badger.[Mother_DIGN_BloodTestsAndResults]
            CROSS APPLY STRING_SPLIT(TestsPeformed, '','') as TestsPeformed
            GROUP BY EntityID, TestsPeformed.value
        ) t
        PIVOT (
            COUNT(Test)
            FOR Test IN(' + @cols + N')
        ) AS pvt;';

    EXEC sp_executesql @sql;
END
GO

使用方式 #

调用存储过程后，直接查询临时表：

EXEC dbo.PopulateBloodTestPivotTempTable;
SELECT * FROM #BloodTestPivot;

方案对比 #

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