none
Convert rows to columns using t-sql. RRS feed

  • Question

  • Hi All, I have a table with below 6 columns.... I want to convert the source records from rows to columns using t-sql.

    Due to sensitive data, I am posting sample data.

    ID,Date, DoctorName, Day  ,  PatientID , PatientName
    1,Sam,  12/02/2019, Monday  , 1 , ABC
    2,Sam,  12/02/2019, Monday  , 2 , XXX
    3,Sam,  12/02/2019, Monday  , 3 , ZZZ
    4,Sam,  12/03/2019, Tuesday , 20 , AAA
    5,Sam,  12/04/2019, Wednesday, 10 , BBB
    6,Dan,  12/03/2019, Tuesday  , 50 , CCC
    7,John, 12/06/2019, Friday  , 100 , YYY

    Output should look like in below format..

    Note: Also, there will be no records for Saturday & sunday , so don't want to display Sat & sun in my output.

    DoctorName, Date, MondayPatientID , MondayPatientName, TuesdayPatientID, TuesdayPatientName, WednesdayPatientID, WednesdayPatientName, ThursdayPatientID, ThursdayPatientName, FridayPatientID, FridayPatientName
    Sam,  12/02/2019,      1          ,   ABC             , 20             , AAA                ,  10               ,   BBB               , null             , null             , null             ,   null
    Sam,  12/02/2019,      2          ,   XXX             , null           , null               , null              ,   null              , null             , null             , null             ,   null
    Sam,  12/02/2019,      3          ,   ZZZ             , null           , null               , null              ,   null              , null             , null             , null             ,   null
    Dan,  12/03/2019,     null        , null              , 50             , CCC                , null              ,   null              , null             , null             , null             ,   null
    John, 12/06/2019,     null        , null              , null           , null               , null              ,   null              , null             , null             , 100              ,   YYY

    Sample data:
    Create table #Sample (ID int ,Date varchar(10), DoctorName varchar(25), Day  varchar(10),  PatientID int , PatientName varchar(25))

    Insert into #Sample (ID,Date, DoctorName, Day  ,  PatientID , PatientName)
    Values (1,'Sam',  '12/02/2019', 'Monday'  , 1 , 'ABC'),
    (2,'Sam',  '12/02/2019', 'Monday'  , 2 , 'XXX'),
    (3,'Sam',  '12/02/2019', 'Monday'  , 3 , 'ZZZ'),
    (4,'Sam',  '12/03/2019', 'Tuesday' , 20 , 'AAA'),
    (5,'Sam',  '12/04/2019', 'Wednesday', 10 , 'BBB'),
    (6,'Dan',  '12/03/2019', 'Tuesday'  , 50 , 'CCC'),
    (7,'John', '12/06/2019', 'Friday'  , 100 , 'YYY')

    Thanks in advance,
    RH


    sql

    • Edited by sql9 Friday, December 6, 2019 8:58 PM added Note
    Friday, December 6, 2019 8:55 PM

Answers

  • Create table #Sample (ID int 
    ,Date varchar(10), DoctorName varchar(25), 
    Day  varchar(10),  PatientID int , PatientName varchar(25))
    
    Insert into #Sample (ID, DoctorName,Date
    , Day  ,  PatientID , PatientName)
    Values (1,'Sam',  '12/02/2019', 'Monday'  , 1 , 'ABC'),
    (2,'Sam',  '12/02/2019', 'Monday'  , 2 , 'XXX'),
    (3,'Sam',  '12/02/2019', 'Monday'  , 3 , 'ZZZ'),
    (4,'Sam',  '12/03/2019', 'Tuesday' , 20 , 'AAA'),
    (5,'Sam',  '12/04/2019', 'Wednesday', 10 , 'BBB'),
    (6,'Dan',  '12/03/2019', 'Tuesday'  , 50 , 'CCC'),
    (7,'John', '12/06/2019', 'Friday'  , 100 , 'YYY')
    
    ;with mycte as 
    (
    select Date, DoctorName,[Day],PatientID , PatientName
    
    ,row_number()Over(Partition by Date, DoctorName Order by PatientID) rn
    
    from #Sample
    )
    
    Select dt as [Date], DoctorName  
    ,max(Case when [Day]='Monday' then PatientID else null end) MondayPatientID  
    ,max(Case when [Day]='Monday' then PatientName else null end) MondayPatientName  
    ,max(Case when [Day]='Tuesday' then PatientID else null end) TuesdayPatientID  
    ,max(Case when [Day]='Tuesday' then PatientName else null end) TuesdayPatientName  
    
    ,max(Case when [Day]='Wednesday' then PatientID else null end) WednesdayPatientID  
    ,max(Case when [Day]='Wednesday' then PatientName else null end) WednesdayPatientName  
    
    ,max(Case when [Day]='Thursday' then PatientID else null end) ThursdayPatientID  
    ,max(Case when [Day]='Thursday' then PatientName else null end) ThursdayPatientName  
    
    ,max(Case when [Day]='Friday' then PatientID else null end) FridayPatientID  
    ,max(Case when [Day]='Friday' then PatientName else null end) FridayPatientName  
    
    from mycte m
    Cross apply (
    select min(date) dt from #Sample t 
    Where m.DoctorName=t.DoctorName  group by DoctorName ) d(dt)
    Group by dt,  DoctorName,rn
    Order by 1
    
    
    drop table #Sample
    
    /*
    Date	DoctorName	MondayPatientID	MondayPatientName	TuesdayPatientID	TuesdayPatientName	WednesdayPatientID	WednesdayPatientName	ThursdayPatientID	ThursdayPatientName	FridayPatientID	FridayPatientName
    12/02/2019	Sam	1	ABC	20	AAA	10	BBB	NULL	NULL	NULL	NULL
    12/02/2019	Sam	2	XXX	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    12/02/2019	Sam	3	ZZZ	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    12/03/2019	Dan	NULL	NULL	50	CCC	NULL	NULL	NULL	NULL	NULL	NULL
    12/06/2019	John	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	100	YYY
    
    */

    Friday, December 6, 2019 9:19 PM
    Moderator

All replies

  • Create table #Sample (ID int 
    ,Date varchar(10), DoctorName varchar(25), 
    Day  varchar(10),  PatientID int , PatientName varchar(25))
    
    Insert into #Sample (ID, DoctorName,Date
    , Day  ,  PatientID , PatientName)
    Values (1,'Sam',  '12/02/2019', 'Monday'  , 1 , 'ABC'),
    (2,'Sam',  '12/02/2019', 'Monday'  , 2 , 'XXX'),
    (3,'Sam',  '12/02/2019', 'Monday'  , 3 , 'ZZZ'),
    (4,'Sam',  '12/03/2019', 'Tuesday' , 20 , 'AAA'),
    (5,'Sam',  '12/04/2019', 'Wednesday', 10 , 'BBB'),
    (6,'Dan',  '12/03/2019', 'Tuesday'  , 50 , 'CCC'),
    (7,'John', '12/06/2019', 'Friday'  , 100 , 'YYY')
    
    ;with mycte as 
    (
    select Date, DoctorName,[Day],PatientID , PatientName
    
    ,row_number()Over(Partition by Date, DoctorName Order by PatientID) rn
    
    from #Sample
    )
    
    Select dt as [Date], DoctorName  
    ,max(Case when [Day]='Monday' then PatientID else null end) MondayPatientID  
    ,max(Case when [Day]='Monday' then PatientName else null end) MondayPatientName  
    ,max(Case when [Day]='Tuesday' then PatientID else null end) TuesdayPatientID  
    ,max(Case when [Day]='Tuesday' then PatientName else null end) TuesdayPatientName  
    
    ,max(Case when [Day]='Wednesday' then PatientID else null end) WednesdayPatientID  
    ,max(Case when [Day]='Wednesday' then PatientName else null end) WednesdayPatientName  
    
    ,max(Case when [Day]='Thursday' then PatientID else null end) ThursdayPatientID  
    ,max(Case when [Day]='Thursday' then PatientName else null end) ThursdayPatientName  
    
    ,max(Case when [Day]='Friday' then PatientID else null end) FridayPatientID  
    ,max(Case when [Day]='Friday' then PatientName else null end) FridayPatientName  
    
    from mycte m
    Cross apply (
    select min(date) dt from #Sample t 
    Where m.DoctorName=t.DoctorName  group by DoctorName ) d(dt)
    Group by dt,  DoctorName,rn
    Order by 1
    
    
    drop table #Sample
    
    /*
    Date	DoctorName	MondayPatientID	MondayPatientName	TuesdayPatientID	TuesdayPatientName	WednesdayPatientID	WednesdayPatientName	ThursdayPatientID	ThursdayPatientName	FridayPatientID	FridayPatientName
    12/02/2019	Sam	1	ABC	20	AAA	10	BBB	NULL	NULL	NULL	NULL
    12/02/2019	Sam	2	XXX	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    12/02/2019	Sam	3	ZZZ	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
    12/03/2019	Dan	NULL	NULL	50	CCC	NULL	NULL	NULL	NULL	NULL	NULL
    12/06/2019	John	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	100	YYY
    
    */

    Friday, December 6, 2019 9:19 PM
    Moderator
  • Thanks Li. It worked and got expected results. I really appreciate your help.

    sql

    Monday, December 9, 2019 10:16 PM