SQL - How do I convert Rows to Columns when there are multiple stored values per attribute? The resu-CodePudding

I need to convert rows to columns and remove duplicate rows.

My table:

PT_ID	ARRIVAL_DATE	UNIT	IN_TYPE	IN_DTTM	OUT_TYPE	OUT_DTTM
X125	06/25/22	FT	Transfer In	6/25/22 18:01	Transfer Out	6/25/22 18:04
X125	06/25/22	PD2	Transfer In	6/25/22 18:04	Discharge	6/25/22 18:59
X588	06/27/22	PD1	Admit	6/27/22 09:30	Transfer Out	6/27/22 13:00
X588	06/27/22	PD2	Transfer In	6/27/22 13:00	Discharge	6/27/22 13:30

I need my results to look like:

PT_ID	ARRIVAL_DATE	FT	PD1	PD2	ADMIT	TRANS_IN_1	TRANS_OUT_1	TRANS_IN_2	TRANS_OUT_2	DISCHARGE
X125	06/25/22	1	NULL	1	NULL	6/25/22 18:01	06/25/22 18:04	06/25/22 18:04	Null	06/25/22 18:59
X588	06/27/22	NULL	1	1	6/27/22 09:30	NULL	06/27/22 13:00	06/27/22 13:00	NULL	06/27/22 13:30

My SQL Code looks like this but I believe it is incorrect. I need to transpose the Trans_In and Trans_Out into columns that represent the 2 different values.

SELECT DBO.[PT_ID], DBO.[ARRIVAL_DATE], 
   max (case when DBO.[UNIT] = 'FT' then '1' end) FT,
   max (case when DBO.[UNIT] = 'PD1' then '1' end) PD1,
   max (case when DBO.[UNIT] = 'PD2' then '1' end) PD2,
   max (case when DBO.[IN_TYPE] = 'Admit' then DBO.[TYPE_DTTM] end) ADMIT,
   min (case when DBO.[IN_TYPE] = 'Transfer In' then DBO.[TYPE_DTTM] end) TRANS_IN_1,
   min (case when DBO.[OUT_TYPE] = 'Transfer Out' then DBO.[TYPE_DTTM] end) TRANS_OUT_1, 
   max (case when DBO.[IN_TYPE] = 'Transfer In' then DBO.[TYPE_DTTM] end) TRAN_IN_2,
   max (case when DBO.[OUT_TYPE] = 'Transfer Out' then DBO.[TYPE_DTTM] end) TRANS_OUT_2,
   max (case when DBO.[OUT_TYPE] = 'Discharge' then DBO.[TYPE_DTTM] end) DISCHARGE, 
 FROM DBO
 WHERE (DBO.[ARRIVAL_DATE] >'06/16/2022'
 GROUP BY DBO.[PT_ID], DBO.[ARRIVAL_DATE]

CodePudding user response：

You are almost done. You just need to add an outter query to aggregate the values of your query using conditions, like this:

SELECT
    pt_id,
    arrival_date,
    ft,
    pd1,
    pd2,
    admit,
    CASE WHEN admit IS NULL THEN trans_in1 END AS trans_in1,
    trans_out1,
    trans_in2,
    CASE WHEN discharge IS NULL THEN trans_out2 END AS trans_out2,
    discharge
FROM (
    SELECT 
        pt_id, 
        arrival_date,
        MAX(CASE WHEN unit = 'FT' THEN 1 END) AS ft,
        MAX (CASE WHEN unit = 'PD1' THEN 1 END) AS pd1,
        MAX(CASE WHEN unit = 'PD2' THEN 1 END) AS pd2,
        MIN(in_dttm) AS trans_in1, 
        MAX(in_dttm) AS trans_in2,
        MIN(out_dttm) As trans_out1,
        MAX(out_dttm) AS trans_out2,
        MAX(CASE WHEN in_type = 'Admit' THEN in_dttm END) AS admit,
        MAX(CASE WHEN out_type = 'Discharge' THEN out_dttm END) AS discharge
    FROM dbo
    GROUP BY pt_id, arrival_date
) t

Please, check a demo.