Dynamic Pivot using Temp tables

I have three query that are all building three separate tables. Procedures, DRGs and Diagnosis. I do not know how many procedures, working DRGs or diagnosis a patient might have. However I only need to certain Procedure Codes, DRGs and Diagnosis Codes. I have each entry ranked in the temp table. Let's just work with one table for now as the answer will be the same for the other two. Let's just work with the Diagnosis Table.

In the Diagnosis Table I have a field for Rank to it will just put in Dx_1, Dx_2, Dx_3 in the Dx_Rank field so I can just pull them out, but I do not know how many Dx there will be. I need these all to be on one line. So the output would be something like this.

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

From a table that looks like this

> 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

So here I have two Patients and the output should look like this.

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, , ,

So I thought I would do a PIVOT. I set everything up and it does not give me what I want. I am getting the Dx_1 in the right Column, but when there is a Dx_2 it is the in right Column but it is on a separate line. I wanted this all on one line as the final output will be going into Excel and I wanted to make it dynamic so if the max diagnosis I get is Dx_3 it would stop and not have 7 extra blank columns. If I hard coded this to have 10 for each and then what happens when I finally get a patient that has 11 and I miss one because I have a limit of 10 Diagnosis.

Here is the script I have so far.

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

I am not getting any errors until I add in the last line a try a select from the Temp Table #PROCEDURES_DX_PIVOT to see what I have. I get an error when I run the code that the Object is not Valid. It is like the code is not being executed. I created a simple test to see if it was a permissions even though I was not getting an error and I was able to create something. I then took the output from that SELECT @SQLPIVOT_DX and that is when I was able to see my result and how I saw if a patient has a Dx_1 and Dx_2 that they are on two separate lines which is not what I want. I am trying to get everything on one line.

Any help would be greatly appreciated. If my approach is not right please let me know.

Solution

To get each Dx_Code of a Patient_Account in the same line, you need to GROUP BY Patient_Account, and use some aggregate function (like MIN() or MAX()) to get the Dx_Code and Dx_Desctiption.

Another way to make a dynamic query:

SELECT STRING_AGG(query_piece, '')

FROM (

(SELECT 'SELECT Patient_Account,' AS query_piece)

UNION ALL

(SELECT 
    CONCAT('MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Code END) AS ', Dx_Rank, ', ', 
    'MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Desctiption END) AS ', Dx_Rank, '_Description, ')
FROM Diagnosis
GROUP BY Dx_Rank)

UNION ALL

(SELECT 'Visit_Key 
    FROM Diagnosis
    GROUP BY Patient_Account, Visit_Key;')) AS dynamic_query;

The result of the dynamic query is the query you have to execute to get the desired result:

SELECT Patient_Account,MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Code END) AS Dx_1, MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Desctiptio END) AS Dx_1_Description, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Code END) AS Dx_2, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Desctiptio END) AS Dx_2_Description, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Code END) AS Dx_3, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Desctiptio END) AS Dx_3_Description, Visit_Key 
    FROM Diagnosis
    GROUP BY Patient_Account, Visit_Key;

Output with the example data in your question:

Patient_Account	Dx_1	Dx_1_Description	Dx_2	Dx_2_Description	Dx_3	Dx_3_Description	Visit_Key
123456789	J20	Left Hip is Broken	A32	Left Knee is broken	R4786	Left Ankle is broken	#PAS203234
987654321	DF346	Right Arm is broken	DT342.12	Right Wrist is broken			#PAS435678

Dynamic query in Sql-Server 2016 (without string_agg() function):

SELECT STUFF((  
        SELECT ' ' + query_piece  
        FROM ((SELECT 'SELECT Patient_Account,' AS query_piece)

        UNION ALL

        (SELECT TOP 100
            CONCAT('MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Code END) AS ', Dx_Rank, ', ', 
            'MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Desctiption END) AS ', Dx_Rank, '_Description, ')
        FROM Diagnosis
        GROUP BY Dx_Rank
        ORDER BY Dx_Rank ASC)

        UNION ALL

        (SELECT 'Visit_Key FROM Diagnosis GROUP BY Patient_Account, Visit_Key')) as x
        FOR XML PATH('')
     ), 1, 1, '');