SQL--combine 3 rows of data into one with only one different column--and using a case
I have the following SQL code:
The data is good, however the output method is not where I want it to be. A patient will appear in multiple rows with this code, on the same EncounterNumber but with multiple diagnoses. For example (I'm including the DX Priority however I don't really need that in my output--I just know that I need it in my code to determine the solution);
DATEOFVISIT MEDICALRECORD ENCOUNTERNUMBER LOCATION FIRSTNAME LASTNAME DOB ZIP ICD DES ICD CODE ENCOUNTER DX PRIORITY
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.45 1
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.15 2
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 E11 3
Again, data is good and it's doing what I ask--however, what I really need is a SINGLE line of data, with two additional columns at the last;
DATEOFVISIT MEDICALRECORD ENCOUNTERNUMBER LOCATION FIRSTNAME LASTNAME DOB ZIP ICD DES PRIMARYDIAG SECONDARYDIAG THIRDDIAG
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.45 R.15 E11
I know that I will be using the Diagnosis Priority as a qualifier to determine what is secondary and third diagnosis on ONE encounter. For example, I tried this in Crystal (where this ultimately ends up);
Then just repeat that for each (secondary and third). However...since it's individual lines of data coming from SQL it still doesn't solve the problem and just blanks out the second/third, even with grouping. So I'm looking for a way to do a CASE or something in SQL to combine those 3 rows of data, that only differ by diagnosis and diagnosis priority, into one. I considered just making a table, doing an initial insert and then updating the other columns (blank if no secondary/third diagnosis is present)--but it seems like a really inelegant way to do it. Thanks for any help.
select DISTINCT CAST(enc_timestamp AS DATE) as [Date of Visit], p.person_nbr AS [Medical Record], enc_nbr AS [Encounter Number],
'Family Medicine' as [Location], p.last_name AS [Last Name], p.first_name AS [First Name], CAST(p.date_of_birth AS DATE) as [Date of Birth],
p.zip AS [Patient Zip Code], 'ICD10' as [ICD Designation], pd.icd9cm_code_id, pd.encounter_dx_priority from patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN payer_mstr pm ON pm.payer_id = pe.cob1_payer_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
JOIN patient_diagnosis pd ON pd.enc_id = pe.enc_id
where CAST(enc_timestamp AS DATE) >= dateadd(day,datediff(day,1,GETDATE()),0)
and pe.billable_ind = 'Y' and pe.clinical_ind = 'Y' and pe.practice_id = '0001' The data is good, however the output method is not where I want it to be. A patient will appear in multiple rows with this code, on the same EncounterNumber but with multiple diagnoses. For example (I'm including the DX Priority however I don't really need that in my output--I just know that I need it in my code to determine the solution);
DATEOFVISIT MEDICALRECORD ENCOUNTERNUMBER LOCATION FIRSTNAME LASTNAME DOB ZIP ICD DES ICD CODE ENCOUNTER DX PRIORITY
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.45 1
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.15 2
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 E11 3
Again, data is good and it's doing what I ask--however, what I really need is a SINGLE line of data, with two additional columns at the last;
DATEOFVISIT MEDICALRECORD ENCOUNTERNUMBER LOCATION FIRSTNAME LASTNAME DOB ZIP ICD DES PRIMARYDIAG SECONDARYDIAG THIRDDIAG
2018/07-11 1111 3423423432 Clinic Test Patient 08/77 55 10 R.45 R.15 E11
I know that I will be using the Diagnosis Priority as a qualifier to determine what is secondary and third diagnosis on ONE encounter. For example, I tried this in Crystal (where this ultimately ends up);
IF {Command.encounter_dx_priority} = 1
THEN {Command.icd9cm_code_id}
ELSE ""Then just repeat that for each (secondary and third). However...since it's individual lines of data coming from SQL it still doesn't solve the problem and just blanks out the second/third, even with grouping. So I'm looking for a way to do a CASE or something in SQL to combine those 3 rows of data, that only differ by diagnosis and diagnosis priority, into one. I considered just making a table, doing an initial insert and then updating the other columns (blank if no secondary/third diagnosis is present)--but it seems like a really inelegant way to do it. Thanks for any help.
Use a Crosstab in Crystal.
ASKER
This will ultimately be in a CSV output, which in my experience Crosstab just doesn't handle well.
I'm researching that pivot article--but it doesn't explain how to do multiple joins within a pivot and I can't seem to find a good example.
I'm researching that pivot article--but it doesn't explain how to do multiple joins within a pivot and I can't seem to find a good example.
What SQL dialect?
The problem with pivoting in SQL is, that SQL is set-based and model bound. Thus the existing PIVOT operator is only for static pivots (known and hard-codede values). Whereas your problem requires a dynamic pivot.
As you said CSV: Why do you need a pivot for CSV? Any data processing of this CSV will be require more effort than using a normalized set.
The problem with pivoting in SQL is, that SQL is set-based and model bound. Thus the existing PIVOT operator is only for static pivots (known and hard-codede values). Whereas your problem requires a dynamic pivot.
As you said CSV: Why do you need a pivot for CSV? Any data processing of this CSV will be require more effort than using a normalized set.
ASKER
Server 2008 R2.
I'm not sure I need a pivot at all. I do need to combine those 3 columns into one row though (Primary, Secondary and Third Diagnosis)--which the table holds as 3 separate rows (By diagnosis priority).
I've tried to figure out how to do this on the presentation side in Crystal using grouping--but since it's coming across via my command as 3 unique rows rather than one row with those 3 column headers--it doesn't work.
Again, I'm considering just making a table--then running an update after the initial insert for secondary and third diagnosis (WHERE Enc_ID = Enc_ID and Diagnosis_Priority =2 for example). But it seems like a really complicated way to do what should be possible in a CASE or AS statement.
I'm not sure I need a pivot at all. I do need to combine those 3 columns into one row though (Primary, Secondary and Third Diagnosis)--which the table holds as 3 separate rows (By diagnosis priority).
I've tried to figure out how to do this on the presentation side in Crystal using grouping--but since it's coming across via my command as 3 unique rows rather than one row with those 3 column headers--it doesn't work.
Again, I'm considering just making a table--then running an update after the initial insert for secondary and third diagnosis (WHERE Enc_ID = Enc_ID and Diagnosis_Priority =2 for example). But it seems like a really complicated way to do what should be possible in a CASE or AS statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nailed it, thanks so much!
There are examples out there.
On the Microsoft site, the example seems similar to what you have: DaysToManufacture versus DX Priority.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017