Transposing repeated rows of datetime into columns I am working with a dataset of inpatient data in SQL Server 2017. 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
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;