How can I get the first 4 rows in one row in separate columns for each visit_id entry?

Question

I have a patient_visit_codes table with fields: pat_id, visit_id, visit_date, visit_code, create_timestamp . Each patient can have an infinite number of visit codes for each visit date. Each visit_code is listed on a separate line. I want to get only the first 4 visit_code created in one row in separate columns.

So, if my table has the following data:

 ;WITH patient_visit_codes AS ( SELECT * FROM (VALUES (1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '3018F', '2015-12-22 07:24:07.403'), (1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '1101F', '2015-12-22 07:28:09.440'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'I50', '2016-01-05 11:05:34.830'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'R50.90', '2016-01-05 11:35:14.560'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'Z02.89', '2016-01-05 11:40:24.740') ) as t (pat_id, visit_id, visit_date, visit_code, create_timestamp)) 

I want to get the results as shown below:

 pat_id | visit_id | visit_code1 | visit_code2 | visit_code3 | visit_code4 1 | B58B3850-43BB-434D-B1EA-B968A01332EA | 3018F | 1101F | Null | Null 56 | 482A73F8-EA3F-4CD6-B1AD-BD2388EC5912 | I50 | R50.90 | Z02.89 | Null 

Answer 1

Using MS SQL 2012, I came up with below to get the desired result. This includes creating a subquery / CTE to rank visits by date, and then 4 independent joins to drop them all into a single row entry:

 WITH cte AS (SELECT pat_ID, visit_ID, visit_date, visit_code, create_timestamp, RANK() OVER(PARTITION BY pat_ID ORDER BY visit_date, create_timestamp) AS RankNo FROM #t) SELECT a.pat_id, a.visit_id, a.visit_code AS visit_code1, b.visit_code AS visit_code2, c.visit_code AS visit_code3, d.visit_code AS visit_code4 FROM cte AS a LEFT JOIN (SELECT * FROM cte AS b WHERE b.RankNo = 2) AS b ON a.pat_ID = b.pat_ID LEFT JOIN (SELECT * FROM cte AS c WHERE c.RankNo = 3) AS c ON a.pat_id = c.pat_ID LEFT JOIN (SELECT * FROM cte AS d WHERE d.RankNo = 4) AS d ON a.pat_ID = d.pat_ID WHERE a.RankNo = 1 

For the RANK () function, I did ORDER BY for both visit_date and create_timestamp to remove the links ... if there is a chance that visit_date will be at the time it was created, you can add a third ORDER BY

This will produce the desired result, but I also suggest dynamic SQL for Googling to see alternative solutions.

Answer 2

You can use XML for this:

 DECLARE @xml xml ;WITH patient_visit_codes AS ( SELECT * FROM (VALUES (1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '3018F', '2015-12-22 07:24:07.403'), (1, 'B58B3850-43BB-434D-B1EA-B968A01332EA', '2015-12-22', '1101F', '2015-12-22 07:28:09.440'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'I50', '2016-01-05 11:05:34.830'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'R50.90', '2016-01-05 11:35:14.560'), (56, '482A73F8-EA3F-4CD6-B1AD-BD2388EC5912', '2016-01-05', 'Z02.89', '2016-01-05 11:40:24.740') ) as t (pat_id, visit_id, visit_date, visit_code, create_timestamp)) , xml_t AS ( SELECT DISTINCT 1 as Tag, 0 as Parent, pat_id as [pat!1!id], visit_id as [pat!1!visit_id], NULL as [visit!2!visit_code] FROM patient_visit_codes UNION ALL SELECT DISTINCT 2 as Tag, 1 as Parent, pat_id as [pat!1!id], visit_id as [pat!1!visit_id], visit_code as [visit!2!visit_code] FROM patient_visit_codes) SELECT @xml = ( SELECT * FROM xml_t ORDER BY [pat!1!id], [pat!1!visit_id],[visit!2!visit_code] FOR XML EXPLICIT) --we get xml like that: --<pat id="1" visit_id="B58B3850-43BB-434D-B1EA-B968A01332EA"> -- <visit visit_code="1101F" /> -- <visit visit_code="3018F" /> --</pat> --<pat id="56" visit_id="482A73F8-EA3F-4CD6-B1AD-BD2388EC5912"> -- <visit visit_code="I50" /> -- <visit visit_code="R50.90" /> -- <visit visit_code="Z02.89" /> --</pat> SELECT Component.value('@id','int') as pat_id, Component.value('@visit_id','nvarchar(32)') as visit_id, Component.value('(visit/@visit_code)[1]','nvarchar(10)') as visit_code1, Component.value('(visit/@visit_code)[2]','nvarchar(10)') as visit_code2, Component.value('(visit/@visit_code)[3]','nvarchar(10)') as visit_code3, Component.value('(visit/@visit_code)[4]','nvarchar(10)') as visit_code4 FROM @xml.nodes('/pat') as X(Component) 

and the result:

 pat_id visit_id visit_code1 visit_code2 visit_code3 visit_code4 ----------- -------------------------------- ----------- ----------- ----------- ----------- 1 B58B3850-43BB-434D-B1EA-B968A013 1101F 3018F NULL NULL 56 482A73F8-EA3F-4CD6-B1AD-BD2388EC I50 R50.90 Z02.89 NULL (2 row(s) affected)