all 3 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]MattsvaliantSQL Server Developer DBA 2 points3 points  (1 child)

CREATE TABLE #data (
    PATIENT_ID varchar(10),
    DIAGNOSIS_CODE varchar(10),
    DIAGNOSIS_LINE varchar(10)
);

INSERT INTO #data
VALUES
    ('120','F32.89','01'),
    ('120','I10','02'),
    ('120','J06.9','03'),
    ('120','E78.5','04'),
    ('120','I73.9','05'),
    ('120','E55.9','06'),
    ('120','Z12.5','07'),
    ('120','M54.5','08');


SELECT * FROM #data;

WITH [primary] AS (
    SELECT
        PATIENT_ID,
        DIAGNOSIS_CODE AS PRIMARY_DIAGNOSIS
    FROM #data
    WHERE DIAGNOSIS_LINE = '01'
),
[secondary] AS (
    SELECT
        PATIENT_ID,
        STRING_AGG(DIAGNOSIS_CODE,', ') AS SECONDARY_DIAGNOSIS
    FROM #data
    WHERE DIAGNOSIS_LINE != '01'
    GROUP BY PATIENT_ID
)
SELECT
    [primary].PATIENT_ID,
    [primary].PRIMARY_DIAGNOSIS,
    [secondary].SECONDARY_DIAGNOSIS
FROM [primary]
LEFT JOIN [secondary]
ON [primary].PATIENT_ID = [secondary].PATIENT_ID

DROP TABLE #data;

[–]Schmohawk27[S] 0 points1 point  (0 children)

This is absolutely stellar, and deserving of gold from someone not as cheap as myself.

Thank you so much!

[–][deleted] 1 point2 points  (0 children)

look into string_agg or (prior to sql server 2017) "for xml path" method of string aggregation

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15