时间戳

首页 > 解决方案 > 使用临时表的动态数据透视

sql - 使用临时表的动态数据透视

问题描述

我有三个查询，它们都在构建三个单独的表。程序、DRG 和诊断。我不知道患者可能有多少程序、有效的 DRG 或诊断。但是我只需要某些程序代码、DRG 和诊断代码。我将每个条目都排在临时表中。现在让我们只使用一张桌子，因为其他两张桌子的答案是一样的。让我们使用诊断表。

在诊断表中，我有一个 Rank 字段，它将在 Dx_Rank 字段中输入 Dx_1、Dx_2、Dx_3，这样我就可以将它们拉出来，但我不知道会有多少 Dx。我需要这些都在一条线上。所以输出会是这样的。

Patient_Account、Visit_Key、Dx_1、Dx_1_Description、Dx_2、Dx_2_Dexcription、Dx_3、Dx_3_Description

从一个看起来像这样的表

> Patient_Account, Visit_Key, Dx_Code, Dx_Priority, Dx_Rank, Dx_Desctiption  
> 123456789, #PAS203234, J20, 3, Dx_1, Left Hip is Broken 
> 123456789, #PAS203234, A32, 6, Dx_2, Left Knee is broken 
> 123456789, #PAS203234, R4786, 8, Dx_3, Left Ankle is broken 
> 987654321, #PAS435678, DF346, 2, Dx_1, Right Arm is broken  
> 987654321, #PAS435678, DT342.12, 4, Dx_2, Right Wrist is broken

所以这里我有两个患者，输出应该是这样的。

Patient_Account, Visit_Key, Dx_1, Dx_1_Description, Dx_2, Dx_2_Description, Dx_3, Dx_3_Description

123456789, #PAS203234, J20, Left Hip is broken, A32, Left Knee is broken, R4786, Left Ankle is broken
987654321, #PAS435678, DF346, Right Arm is broken, DT342.12, Right Wrist is broken, , ,

所以我想我会做一个 PIVOT。我设置了所有东西，但它并没有给我想要的东西。我在右列中得到 Dx_1，但是当有 Dx_2 时，它在右列中，但它位于单独的行上。我希望这一切都在一行上，因为最终输出将进入 Excel，我想让它动态化，所以如果我得到的最大诊断是 Dx_3，它将停止并且没有 7 个额外的空白列。如果我将其硬编码为每个有 10 个，然后当我最终得到一个有 11 个的患者并且我错过了一个，因为我的诊断限制为 10 时会发生什么。

这是我到目前为止的脚本。

IF OBJECT_ID('tempdb..#PROCEDURES_CPT') IS NOT NULL 
    DROP TABLE #PROCEDURES_CPT
IF OBJECT_ID('tempdb..#PROCEDURES_CPT_PIVOT') IS NOT NULL 
DROP TABLE #PROCEDURE_CPT_PIVOT

IF OBJECT_ID('tempdb..#PROCEDURES_DRG') IS NOT NULL 
DROP TABLE #PROCEDURES_DRG
IF OBJECT_ID('tempdb..#PROCEDURES_DRG_PIVOT') IS NOT NULL 
DROP TABLE #PROCEDURES_DRG_PIVOT

IF OBJECT_ID('tempdb..#PROCEDURES_DX') IS NOT NULL 
DROP TABLE #PROCEDURES_DX
IF OBJECT_ID('tempdb..#PROCEDURES_DX_PIVOT') IS NOT NULL 
DROP TABLE #PROCEDURES_DX_PIVOT

IF OBJECT_ID('tempdb..#PATIENTS') IS NOT NULL 
DROP TABLE #PATIENTS
IF OBJECT_ID('tempdb..#PATIENTS_FINAL') IS NOT NULL 
DROP TABLE #PATIENTS_FINAL

/** DEFINE TABLE AND PARAMETERS  **/

/** CREATE THE TABLE FOR THE PATIENTS  **/
CREATE TABLE #PATIENTS
(
        Patient_Account VARCHAR(20) NOT NULL,
        Visit_Key       VARCHAR(20) NOT NULL
)

/**  DECLARE THE VARIABLES FOR THE TEMP PIVOT TABLES THAT ARE NEEDED FOR THE THREE SECTIONS  **/
DECLARE @SQLPIVOT_CPT AS NVARCHAR(MAX)
DECLARE @SQLPIVOT_DRG AS NVARCHAR(MAX)
DECLARE @SQLPIVOT_DX AS NVARCHAR(MAX)

/**  DECLARE THE VARIABLE NAME THAT WILL HOLD THE COLUMN NAMES LIKE CPT_1, CPT2, CPT_3, etc OR DRG_1, DRG_2,etc, OR DX_1, DX_2, DX_3, DX_4, etc  **/
DECLARE @PivotColumns_CPT AS NVARCHAR(MAX)
DECLARE @PivotColumns_DRG AS NVARCHAR(MAX)
DECLARE @PivotColumns_DX AS NVARCHAR(MAX)


/** CPT CODES  **/

SELECT      pv.pt_id,
            pv.vst_key,
            pv.proc_eff_full_date,
            pv.prio_cd,
            'CPT_' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY pv.pt_id ORDER BY pv.prio_cd)) AS 'CPT_Rank',
            pv.proc_cd,
            pv.alt_clasf_desc,
            hrp.Procedure_Type,
            UPPER(pv.resp_pract_rpt_name) AS 'Surgeon'
INTO        #PROCEDURES_CPT
FROM        smsdss.proc_v AS pv INNER JOIN
            dbo.BETHESDA_HIGH_RISK_PROCEDURES AS hrp ON pv.proc_cd = hrp.Clinical_Code AND hrp.Report_Type = 'CPT'
WHERE       pv.proc_eff_full_date BETWEEN '10/01/2020' AND '09/30/2021'

/** DRG CODES  **/

SELECT      vv.pt_id,
            vv.vst_key,
            vv.end_full_date,
            vv.drg_no,
            UPPER(drg.DRGDesc) AS 'DRG_Description',
            hrp.Procedure_Type,
            UPPER(vv.adm_pract_rpt_name) AS 'Admitting Physician'
INTO        #PROCEDURES_DRG
FROM        smsdss.vst_v AS vv INNER JOIN
            dbo.BETHESDA_HIGH_RISK_PROCEDURES AS hrp ON vv.drg_no = hrp.Clinical_Code AND hrp.Report_Type = 'DRG' LEFT OUTER JOIN
            smsdss.DRGMstr AS drg ON vv.drg_no = drg.DRGNo  AND drg.DRGVers = 'MS-V38'
WHERE       vv.end_full_date BETWEEN '10/01/2020' AND '09/30/2021'

/** DX CODES  **/

SELECT      dx.pt_id,
            dx.vst_key,
            dx.dx_eff_full_date,
            dx.dx_type_desc,
            'Dx_' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(PARTITION BY dx.pt_id, dx.dx_type_desc ORDER BY dx.prio_cd)) AS 'Dx_Rank',
            dx.prio_cd,
            dx.dx_cd,
            dx.clasf_desc,
            hrp.Procedure_Type 
INTO        #PROCEDURES_DX
FROM        smsdss.dx_grp_v AS dx INNER JOIN
            dbo.BETHESDA_HIGH_RISK_PROCEDURES AS hrp ON dx.dx_cd = hrp.Clinical_Code AND hrp.Report_Type = 'ICD-10'
WHERE       dx.dx_eff_full_date BETWEEN '10/01/2020' AND '09/30/2021'
GROUP BY    dx.pt_id,
            dx.vst_key,
            dx.dx_eff_full_date,
            dx.dx_type_desc,
            dx.prio_cd,
            dx.dx_cd,
            dx.clasf_desc,
            hrp.Procedure_Type 
ORDER BY    dx.pt_id,
            dx.dx_type_desc,
            dx.prio_cd
 
 /** PIVOT DX  **/
 SELECT     @PivotColumns_DX = COALESCE(@PivotColumns_DX +  ', ' , '') + Dx_Rank
 FROM       #PROCEDURES_DX 
 GROUP BY   Dx_Rank
 ORDER BY   Dx_Rank

SELECT @PivotColumns_DX

 SET @SQLPIVOT_DX = N'SELECT    pt_id, vst_key, ' + @PivotColumns_DX + ' 
                      INTO      #PROCEDURES_DX_PIVOT
                      FROM      #PROCEDURES_DX
                            PIVOT (MAX(dx_cd)
                                FOR Dx_Rank IN (' + @PivotColumns_DX + ')) AS tdx'
SELECT @SQLPIVOT_DX

EXEC sp_executesql @SQLPIVOT_DX

SELECT * FROM #PROCEDURES_DX_PIVOT

直到我在最后一行添加尝试从 Temp Table #PROCEDURES_DX_PIVOT 中选择以查看我拥有的内容之前，我没有收到任何错误。运行对象无效的代码时出现错误。就像代码没有被执行一样。我创建了一个简单的测试来查看它是否是一个权限，即使我没有收到错误并且我能够创建一些东西。然后我从中获取输出，SELECT @SQLPIVOT_DX那时我能够看到我的结果以及我如何看到患者是否有 Dx_1 和 Dx_2，它们位于两条不同的行上，这不是我想要的。我试图把所有东西都放在一条线上。

任何帮助将不胜感激。如果我的方法不正确，请告诉我。

标签： sqlsql-servertsqldynamic-pivot

解决方案

美好的一天，不幸的是，您没有提供所有相关实体来重现您的场景并执行您的完整查询，但最后一个主要问题似乎很清楚

直到我在最后一行添加尝试从 Temp Table #PROCEDURES_DX_PIVOT 中选择以查看我拥有的内容之前，我没有收到任何错误。运行对象无效的代码时出现错误。就像代码没有被执行一样。

问题是您在 sp_executesql 的执行范围内创建了临时表，但您尝试在此范围之外使用它并且它不存在

这是同一问题的简单完整演示

准备

USE tempdb
GO
DROP TABLE IF EXISTS T
GO 
create table T(id int)
GO

我们在我们称之为的同一范围内创建临时表的工作示例

DROP TABLE IF EXISTS #PROCEDURES_DX_PIVOT
GO 
SELECT * INTO #PROCEDURES_DX_PIVOT FROM T
GO
SELECT * FROM #PROCEDURES_DX_PIVOT
GO -- OK, since the temp table was created in the same scope as we call it

错误！无效的对象名称“#PROCEDURES_DX_PIVOT”

DROP TABLE IF EXISTS #PROCEDURES_DX_PIVOT
GO 
DECLARE @SQLPIVOT_DX NVARCHAR(MAX)
SET @SQLPIVOT_DX = N'SELECT * INTO #PROCEDURES_DX_PIVOT FROM T'
SELECT @SQLPIVOT_DX
EXEC sp_executesql @SQLPIVOT_DX

SELECT * FROM #PROCEDURES_DX_PIVOT
GO -- Invalid object name '#PROCEDURES_DX_PIVOT'

可选的解决方案是从动态查询内部调用表

DROP TABLE IF EXISTS #PROCEDURES_DX_PIVOT
GO 
DECLARE @SQLPIVOT_DX NVARCHAR(MAX)
SET @SQLPIVOT_DX = N'SELECT * INTO #PROCEDURES_DX_PIVOT FROM T; SELECT * FROM #PROCEDURES_DX_PIVOT;'
SELECT @SQLPIVOT_DX
EXEC sp_executesql @SQLPIVOT_DX
GO -- Invalid object name '#PROCEDURES_DX_PIVOT'

返回您的代码

这与您的代码中的问题相同：

SET @SQLPIVOT_DX = N'SELECT    pt_id, vst_key, ' + @PivotColumns_DX + ' 
                      INTO      #PROCEDURES_DX_PIVOT
                      FROM      #PROCEDURES_DX
                            PIVOT (MAX(dx_cd)
                                FOR Dx_Rank IN (' + @PivotColumns_DX + ')) AS tdx'
SELECT @SQLPIVOT_DX

EXEC sp_executesql @SQLPIVOT_DX -- here you use select into to create the temp table

SELECT * FROM #PROCEDURES_DX_PIVOT --  and here you use it outside of the scope which it was created

推荐阅读