Xrhrft

I am working with a dataset of inpatient data in SQL version 17. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, the ID field is not unique. The table looks like this:

ID       AdmDate1      DischDate    

10001   2012-10-16    2012-10-26 

10001   2014-06-15    2014-06-18 

10001   2014-12-21    2014-12-29 

10002   2013-02-14    2013-02-20 

10003   2013-01-23    2013-01-31 

10004   2012-11-15    2012-11-19 

10004   2014-09-26    2014-10-06 

10005   2014-12-12    2014-12-23 

10006   2013-10-23    2013-10-28 

Since I want to calculate readmission rate and intervals between readmissions, I want to create a table like this:

ID       AdmDate1     AdmDate2    AdmDate3   DischDate1 DischDate2 DischDate3   

10001   2012-10-16   2014-06-15  2014-12-21

10002   2013-02-14   None        None

Could anyone help me?
Thanks in advance.

I have counted the rows in the columns of AdmDate1 and found the
maximum admission frequency is 35. Since readmissions more than 4 are
not many, I can remove the rows with rn>4 for AdmDate1 and DischDate.
by doing this, the number of columns to be created will be reduced to
manageable numbers

Based on this reply i started looking for a solution which uses 4 of admdates and 4 of Dischdates.

So this works, but I don't dare look at the query plan

Create test data

create table AdmissionDates(id int, AdmDate1 date,  DischDate date)

insert into AdmissionDates(id,AdmDate1,DischDate)

VALUES(  10001 ,  '2012-10-16' ,  '2012-10-26'),

(10001 ,  '2014-06-15'   , '2014-06-18')  ,

(10001 ,  '2014-12-21'   , '2014-12-29')  ,

(10002 ,  '2013-02-14'   , '2013-02-20')  ,

(10003 ,  '2013-01-23'   , '2013-01-31')  ,

(10004 ,  '2012-11-15'   , '2012-11-19')  ,

(10004 ,  '2014-09-26'   , '2014-10-06')  ,

(10005 ,  '2014-12-12'   , '2014-12-23')  ,

(10006 ,  '2013-10-23'   , '2013-10-28')  



select id,row_number() over( partition by id order by admDate1 asc) as rownum, AdmDate1,row_number() over( partition by id order by DischDate asc) as rownum2, DischDate

into #temp

from AdmissionDates;

The Query

select DISTINCT id, 

                t2.admDate1 as admDate1,

                t3.admDate1 as admDate2,

                t4.admDate1 as admDate3,

                t5.admDate1 as admDate4, 

                T6.DischDate as DischDate1,

                T7.DischDate as DischDate2,

                T8.DischDate as DischDate3,

                T9.DischDate as DischDate4

FROM 

#temp t1

OUTER APPLY 

(select admDate1 from #temp T2 where rownum = 1 and T2.id = T1.id) as T2

OUTER APPLY 

(select admDate1 from #temp T2 where rownum = 2 and T2.id = T1.id) as T3

OUTER APPLY 

(select admDate1 from #temp T2 where rownum = 3 and T2.id = T1.id) as T4

OUTER APPLY 

(select admDate1 from #temp T2 where rownum = 4 and T2.id = T1.id) as T5

OUTER APPLY 

(select DischDate from #temp T2 where rownum2 = 1 and T2.id = T1.id) as T6

OUTER APPLY 

(select DischDate from #temp T2 where rownum2 = 2 and T2.id = T1.id) as T7

OUTER APPLY 

(select DischDate from #temp T2 where rownum2 = 3 and T2.id = T1.id) as T8

OUTER APPLY 

(select DischDate from #temp T2 where rownum2 = 4 and T2.id = T1.id) as T9;





DROP TABLE #temp;