SQL: Including Individual Diagnoses in a Single Horizontal Line

Question

I use Microsoft SQL Server 2008 to organize mental health.

I have a table that lists all the clients and their diagnoses, but each diagnosis that the client has on a new line. I want all of them to be on the same line, which are listed horizontally with the date of each diagnosis. Some people have only one diagnosis, some have 20, some do not.

Here is an example of what my data type looks like (only with a large number of clients, we have thousands):

And here is the format in which I would like:

Any solutions you could offer or tips in the right direction would be great, thanks!

Answer 1

To get the result, I would first turn off and then rotate your data. The non-repetition will take the date and diagnostic columns and convert them to strings. Once the data is in rows, then you can apply a support bar.

If you have a known number of values, you can program your query in the same way:

select * from ( select person, [case#], age, col+'_'+cast(rn as varchar(10)) col, value from ( select person, [case#], age, diagnosis, convert(varchar(10), diagnosisdate, 101) diagnosisDate, row_number() over(partition by person, [case#] order by DiagnosisDate) rn from yourtable ) d cross apply ( values ('diagnosis', diagnosis), ('diagnosisDate', diagnosisDate) ) c (col, value) ) t pivot ( max(value) for col in (diagnosis_1, diagnosisDate_1, diagnosis_2, diagnosisDate_2, diagnosis_3, diagnosisDate_3, diagnosis_4, diagnosisDate_4) ) piv; 

See SQL Fiddle with Demo .

I am going to assume that you will have an unknown number of diagnosis values ​​for each case. If so, then you will need to use dynamic sql to generate the result:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(col+'_'+cast(rn as varchar(10))) from ( select row_number() over(partition by person, [case#] order by DiagnosisDate) rn from yourtable ) t cross join ( select 'Diagnosis' col union all select 'DiagnosisDate' ) c group by col, rn order by rn, col FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT person, [case#], age,' + @cols + ' from ( select person, [case#], age, col+''_''+cast(rn as varchar(10)) col, value from ( select person, [case#], age, diagnosis, convert(varchar(10), diagnosisdate, 101) diagnosisDate, row_number() over(partition by person, [case#] order by DiagnosisDate) rn from yourtable ) d cross apply ( values (''diagnosis'', diagnosis), (''diagnosisDate'', diagnosisDate) ) c (col, value) ) t pivot ( max(value) for col in (' + @cols + ') ) p ' execute(@query); 

See SQL Fiddle with Demo . Both queries give the result:

 | PERSON | CASE# | AGE | DIAGNOSIS_1 | DIAGNOSISDATE_1 | DIAGNOSIS_2 | DIAGNOSISDATE_2 | DIAGNOSIS_3 | DIAGNOSISDATE_3 | DIAGNOSIS_4 | DIAGNOSISDATE_4 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | John | 13784 | 56 | Depression | 03/13/2012 | Brain Injury | 03/14/2012 | Spinal Cord Injury | 03/15/2012 | Hypertension | 03/16/2012 | | Kate | 2643 | 37 | Bipolar | 03/11/2012 | Hypertension | 03/12/2012 | (null) | (null) | (null) | (null) | | Kevin | 500934 | 25 | Down Syndrome | 03/18/2012 | Clinical Obesity | 03/19/2012 | (null) | (null) | (null) | (null) | | Pete | 803342 | 34 | Schizophenia | 03/17/2012 | (null) | (null) | (null) | (null) | (null) | (null) | 

Answer 2

For this type of rotation, I think the aggregate / group method is possible:

 select d.case, d.person, max(case when seqnum = 1 then diagnosis end) as d1, max(case when seqnum = 1 then diagnosisdate end) as d1date, max(case when seqnum = 2 then diagnosis end) as d2, max(case when seqnum = 2 then diagnosisdate end) as d2date, . . . -- and so on, for as many groups that you want from (select d.*, row_number() over (partition by case order by diagnosisdate) as seqnum from diagnoses d ) d group by d.case, d.person 

Answer 3

Since you are dealing with confidential medical information, identifiable information (age of names, etc.) should not be stored in the same table as the medical information. In addition, if you extract information about a person into your own table and diagnostic table, which has its own key personID, you can establish a 1 to many relationship.

Answer 4

If you are not using Dynamic SQL, the PIVOT statement will not work. I guess patients can come on any date. The PIVOT operator works with a finite and predefined number of columns. Your options are to use Dynamic SQL to create a PIVOT table, or use Excel or a reporting tool such as SSRS for a Pivot report.

I think that the Dynamic SQL option here would not be practical, since you could have hundreds of columns for each date a patient was visited.

If you want to learn the Dynamic SQL parameter anyway, look here:

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/