如何为动态列的SQL动态透视查询创建视图并对接Access

首先，咱们得明确核心问题：SQL Server的视图要求创建时就固定列数和列名，但你的动态透视查询列数是随数据变化的（比如OriginalDx1、OriginalDx2这类），所以直接创建视图根本行不通。不过别担心，有两个靠谱的方案能解决你的需求，对接Access还不会断链接。

方案1：用存储过程替代视图（推荐，实时数据） #

把你写的整个动态SQL逻辑封装成一个存储过程，然后在Access里通过传递查询调用它，这样既能保留动态列的灵活性，又能让团队在Access里基于它创建更多查询。

步骤1：创建存储过程 #

在SQL Server里执行下面的代码，把你的动态逻辑打包成存储过程：

CREATE PROCEDURE dbo.GetHCCCodingPivotedData
AS
BEGIN
    SET NOCOUNT ON;

    -- 原动态SQL代码全部保留
    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
    SELECT @cols = STUFF((SELECT ',' + QUOTENAME(case when d.col = 'OriginalDx' then col+cast(seq as varchar(10)) else 'OriginalDx'+cast(seq as varchar(10))+'_'+col end)
    from (
    select row_number() over(partition by [HCCCodingBASEID] order by [HCCCodingBASEID]) seq
    from [IDEAApplication].[HCCCoding].[OriginalDiagnosis]
    ) t
    cross apply (
    select 'OriginalDx', 1
    ) d (col, so)
    group by col, so, seq
    order by seq, so
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
    set @query = 'SELECT [HCCCodingBASEID] AS ORIGINALDX_ID, ' + @cols + ' 
    from (
    select t.[HCCCodingBASEID],
    col = case when c.col = ''OriginalDx'' then col+cast(seq as varchar(10)) else ''OriginalDx''+cast(seq as varchar(10))+''_''+col end,
    value
    from (
    select [HCCCodingBASEID], [OriginalDiagnosisCD], row_number() over(partition by [HCCCodingBASEID] order by [HCCCodingBASEID]) seq
    from [IDEAApplication].[HCCCoding].[OriginalDiagnosis]
    ) t
    cross apply (
    select ''OriginalDx'', [OriginalDiagnosisCD]
    ) c (col, value)
    ) x
    pivot (
    max(value) for col in (' + @cols + ')
    ) p '

    DECLARE @cols1 AS NVARCHAR(MAX), @query1 AS NVARCHAR(MAX)
    select @cols1 = STUFF((SELECT ',' + QUOTENAME(case when d.col = 'DxAdded' then col+cast(seq as varchar(10)) else 'DxAdded'+cast(seq as varchar(10))+'_'+col end)
    from (
    select row_number() over(partition by [HCCCodingBASEID] order by [AddDiagnosisCD]) seq
    from [IDEAApplication].[HCCCoding].[AddDiagnosis]
    ) t1
    cross apply (
    select 'DxAdded', 1
    union all
    select 'Reason', 2
    ) d (col, so)
    group by col, so, seq
    order by seq, so
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
    set @query1 = 'SELECT [HCCCodingBASEID] AS DXADDED_ID, ' + @cols1 + ' 
    from (
    select t1.[HCCCodingBASEID],
    col = case when c.col = ''DxAdded'' then col+cast(seq as varchar(10)) else ''DxAdded''+cast(seq as varchar(10))+''_''+col end,
    value
    from (
    select [HCCCodingBASEID], [AddDiagnosisCD], [AddReasonTXT], row_number() over(partition by [HCCCodingBASEID] order by [AddDiagnosisCD]) seq
    from [IDEAApplication].[HCCCoding].[AddDiagnosis]
    ) t1
    cross apply (
    select ''DxAdded'', [AddDiagnosisCD]
    union all
    select ''Reason'', [AddReasonTXT]
    ) c (col, value)
    ) x
    pivot (
    max(value) for col in (' + @cols1 + ')
    ) p '

    DECLARE @cols2 AS NVARCHAR(MAX), @query2 AS NVARCHAR(MAX)
    select @cols2 = STUFF((SELECT ',' + QUOTENAME(case when d.col = 'DxDeleted' then col+cast(seq as varchar(10)) else 'DxDeleted'+cast(seq as varchar(10))+'_'+col end)
    from (
    select row_number() over(partition by [HCCCodingBASEID] order by [DeleteDiagnosisCD]) seq
    from [IDEAApplication].[HCCCoding].[DeleteDiagnosis]
    ) t2
    cross apply (
    select 'DxDeleted', 1
    union all
    select 'Reason', 2
    ) d (col, so)
    group by col, so, seq
    order by seq, so
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')
    set @query2 = 'SELECT [HCCCodingBASEID] AS DXDELETED_ID, ' + @cols2 + ' 
    from (
    select t2.[HCCCodingBASEID],
    col = case when c.col = ''DxDeleted'' then col+cast(seq as varchar(10)) else ''DxDeleted''+cast(seq as varchar(10))+''_''+col end,
    value
    from (
    select [HCCCodingBASEID], [DeleteDiagnosisCD], [DeleteReasonTXT], row_number() over(partition by [HCCCodingBASEID] order by [DeleteDiagnosisCD]) seq
    from [IDEAApplication].[HCCCoding].[DeleteDiagnosis]
    ) t2
    cross apply (
    select ''DxDeleted'', [DeleteDiagnosisCD]
    union all
    select ''Reason'', [DeleteReasonTXT]
    ) c (col, value)
    ) x
    pivot (
    max(value) for col in (' + @cols2 + ')
    ) p '

    DECLARE @cmd NVARCHAR(MAX);
    SET @cmd=N' SELECT A.*, BB.PatientID as [Epic PatientID], convert (varchar (10),AA.BirthDTS,101) as [Birth Date], AA.SexDSC as [Sex], BB.HospitalAccountBaseClassDSC as [Patient Type], EE.payorNM AS [Payor Name], B.*, C.*, D.*
    FROM [IDEAApplication].[HCCCoding].[HCCCoding] as A
    LEFT JOIN ('+@query+') AS B ON A.ID=B.ORIGINALDX_ID
    LEFT JOIN ('+@query1+') AS C ON A.ID=C.DXADDED_ID
    LEFT JOIN ('+@query2+') AS D ON A.ID=D.DXDELETED_ID
    LEFT OUTER JOIN Epic.Finance.HospitalAccount_Enterprise AS BB ON A.HospitalAccountID = BB.HospitalAccountID
    LEFT JOIN Epic.Patient.Patient_Enterprise AS AA ON BB.PatientID =AA.PatientID
    LEFT JOIN Epic.Finance.HospitalAccount3_Enterprise AS CC ON BB.HospitalAccountID = CC.HospitalAccountID
    LEFT JOIN Epic.Reference.Payor AS EE ON BB.PrimaryPayorID = EE.PayorID
    order BY A.ID
     ; '
    EXECUTE sp_executesql @cmd;
END

步骤2：在Access中调用存储过程 #

1. 确保Access已经通过ODBC链接到你的SQL Server数据库。
2. 点击Access的创建选项卡，选择查询设计，关闭弹出的“显示表”窗口。
3. 在设计选项卡中点击传递按钮，打开传递查询编辑器。
4. 在查询属性里，设置“ODBC连接字符串”为你的SQL Server链接（或者选择已有的链接表数据源）。
5. 在SQL输入框里写：EXEC dbo.GetHCCCodingPivotedData;
6. 保存这个查询，比如命名为qry_HCCCodingPivoted。

现在团队就能基于这个传递查询创建其他Access查询了——即使动态列的数量变化，存储过程会自动生成最新的列，只要右键刷新Access查询的字段列表就能识别新列，链接不会断。

方案2：永久表+视图+定时刷新（适合非实时需求） #

如果需要更像“普通视图/表”的体验（比如能直接在Access里当作表编辑），可以用这个方案：

步骤1：创建刷新数据的存储过程 #

这个存储过程会自动创建/更新一个永久表，把动态透视的结果存进去：

CREATE PROCEDURE dbo.RefreshHCCCodingPivotedTable
AS
BEGIN
    SET NOCOUNT ON;

    -- 删除旧表（如果结构变化）
    IF OBJECT_ID('dbo.HCCCodingPivoted', 'U') IS NOT NULL
        DROP TABLE dbo.HCCCodingPivoted;

    -- 用动态SQL创建并填充新表
    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
    -- 这里复制你原有的动态SQL代码（和方案1里的一样）
    -- ... 省略重复代码，最后把@cmd改成：
    SET @cmd=N' SELECT A.*, BB.PatientID as [Epic PatientID], convert (varchar (10),AA.BirthDTS,101) as [Birth Date], AA.SexDSC as [Sex], BB.HospitalAccountBaseClassDSC as [Patient Type], EE.payorNM AS [Payor Name], B.*, C.*, D.*
    INTO dbo.HCCCodingPivoted
    FROM [IDEAApplication].[HCCCoding].[HCCCoding] as A
    LEFT JOIN ('+@query+') AS B ON A.ID=B.ORIGINALDX_ID
    LEFT JOIN ('+@query1+') AS C ON A.ID=C.DXADDED_ID
    LEFT JOIN ('+@query2+') AS D ON A.ID=D.DXDELETED_ID
    LEFT OUTER JOIN Epic.Finance.HospitalAccount_Enterprise AS BB ON A.HospitalAccountID = BB.HospitalAccountID
    LEFT JOIN Epic.Patient.Patient_Enterprise AS AA ON BB.PatientID =AA.PatientID
    LEFT JOIN Epic.Finance.HospitalAccount3_Enterprise AS CC ON BB.HospitalAccountID = CC.HospitalAccountID
    LEFT JOIN Epic.Reference.Payor AS EE ON BB.PrimaryPayorID = EE.PayorID
    order BY A.ID
     ; '
    EXECUTE sp_executesql @cmd;
END

步骤2：创建视图指向永久表 #

CREATE VIEW dbo.vw_HCCCodingPivoted
AS
SELECT * FROM dbo.HCCCodingPivoted;

步骤3：设置定时刷新 #

在SQL Server代理里创建一个作业，定期执行dbo.RefreshHCCCodingPivotedTable，比如每天凌晨刷新一次，保证数据不会太旧。

步骤4：在Access中链接视图 #

把vw_HCCCodingPivoted作为链接表导入Access，团队就能像使用普通表一样操作了。如果列结构变化，右键链接表选择“刷新”即可更新字段列表。

方案对比 #

根据你的需求，优先选方案1，它完全适配动态列的场景，对接Access也简单，还不用操心定时刷新的事儿。

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