Convert 7 columns in two rows to 14 columns in one row

All we need is an easy explanation of the problem, so here it is.

I need a T-SQL script to change two rows to one row. The rows are grouped by Student ID and Unique Course Identifier. The second row columns in the groups for the Days, Start Time, End Time, Start Date, End Date, Building and Room need to be on the first row in the columns on the right which are Days2, Start Time2, End Time2, Start Date2, End Date2, Building2 and Room2. There’s no aggregate so it doesn’t seem like I would want to use pivot. It looks like it might be possible with Union All or for xml path. What is the script for that? SQL Server 2014

Convert 7 columns in two rows to 14 columns in one row

Table Schema

CREATE TABLE [dbo].[Acc_Schedule_History](
    [Run Date] [datetime] NULL,
    [Student ID] [int] NOT NULL,
    [Unique Course Identifier] [varchar](255) NULL,
    [Course Title] [char](35) NULL,
    [Term] [char](6) NOT NULL,
    [Email] [char](60) NULL,
    [Days] [varchar](20) NULL,
    [Section Number] [varchar](20) NULL,
    [Start Time] [datetime] NULL,
    [End Time] [datetime] NULL,
    [Start Date] [datetime] NULL,
    [End Date] [datetime] NULL,
    [Building] [varchar](20) NULL,
    [Room] [varchar](20) NULL,
    [Days2] [varchar](20) NULL,
    [Start Time2] [datetime] NULL,
    [End Time2] [datetime] NULL,
    [Building2] [varchar](20) NULL,
    [Room2] [varchar](20) NULL,
    [Enrolled] [char](1) NULL,
    [Enrollment End Date] [datetime] NULL,
    [SCH_ORIGINAL_REG_TIM] [datetime] NULL,
    [SCH_ADD_DTE] [datetime] NULL,
    [SCH_DROP_DTE] [datetime] NULL,
    [SM_YR_CDE] [char](6) NULL,
    [SM_TRM_CDE] [char](6) NULL,
    [YT_TRM_BEGIN_DTE] [datetime] NULL,
    [YT_TRM_END_DTE] [datetime] NULL,
    [SCH_BEGIN_DTE] [datetime] NULL,
    [SCH_END_DTE] [datetime] NULL,
    [SS_BEGIN_DTE] [datetime] NULL,
    [SS_END_DTE] [datetime] NULL
) ON [PRIMARY]

Sample data

Student ID,Unique Course Identifier,Course Title,Term,Section Number,Days,Start Time,End Time,Start Date,End Date,Building,Room,Days2,Start Time2,End Time2,Start Date2,End Date2,Building2,Room2
119527,BASC 5101 001 WINTER 2022_202202_001,BIOLOGY OF CELLS AND TISSUE,202202,1,T,10:00:00 AM,11:50:00 AM,5/2/2022,8/12/2022,EAST,E110,,,,,,,
119527,BASC 5101 001 WINTER 2022_202202_001,BIOLOGY OF CELLS AND TISSUE,202202,1,W,9:00:00 AM,10:50:00 AM,5/2/2022,8/12/2022,ONLN,ONLN,,,,,,,
1345152,CHSC 7402 001 FALL 2022_202201_001,SOT,202201,1,M,9:01:00 PM,9:59:00 PM,5/2/2022,8/12/2022,ONLN,ONLN,,,,,,,
1564136,AMM  0102 002 FALL 2022_202201_0102,Pathology for Massage Professional,202201,102,TRS,6:00:00 PM,10:00:00 PM,3/10/2022,4/12/2022,CHS,121,,,,,,,
1564136,HHM  0101 002 FALL 2022_202201_0101,Human Health & Hygiene,202201,101,MWF,6:00:00 PM,10:00:00 PM,4/4/2022,4/13/2022,ONLN,ONLN,,,,,,,
2164136,HHM  0102 002 FALL 2022_202201_0102,Nutrition,202201,102,MWF,12:00:00 AM,12:00:00 PM,4/11/2022,4/13/2022,ONLN,ONLN,,,,,,,
2164136,HHM  0102 002 FALL 2022_202201_0102,Nutrition,202201,102,R,6:00:00 PM,10:00:00 PM,4/14/2022,4/14/2022,ONLN,ONLN,,,,,,,
2365362,CHSC 7402 001 WINTER 2022_202202_001,SOT,202202,1,M,9:01:00 PM,9:59:00 PM,5/2/2022,8/12/2022,ONLN,ONLN,,,,,,,
2365362,CHSC 7409 001 WINTER 2022_202203_001,ACTIVATOR I TECHNIQUE,202203,1,T,9:01:00 PM,9:59:00 PM,5/2/2022,8/12/2022,ONLN,ONLN,,,,,,,
9999935,BASC 5101 001 FALL 2022_202201_001,BIOLOGY OF CELLS AND TISSUE,202201,1,T,10:00:00 AM,11:50:00 AM,5/2/2022,8/12/2022,EAST,E110,,,,,,,
9999935,BASC 5101 001 FALL 2022_202201_001,BIOLOGY OF CELLS AND TISSUE,202201,1,W,9:00:00 AM,10:50:00 AM,5/2/2022,8/12/2022,ONLN,ONLN,,,,,,,

I tried

cteFirstGroup
as
(
Select ROW_NUMBER() OVER (Partition by [Student ID], [Unique Course Identifier] ORDER BY [Student ID]) row_num,
       [Student ID]
      ,[Unique Course Identifier]
      ,[Course Title]
      ,[Term]
      ,[Email]
      ,[Days]
      ,[Section Number]
      ,[Start Time]
      ,[End Time]
      ,[Start Date]
      ,[End Date]
      ,[Building]
      ,[Room]
      ,[Days2]
      ,[Start Time2]
      ,[End Time2]
      ,[Building2]
      ,[Room2]
      ,[Enrolled]
      ,[Enrollment End Date]
      FROM cteAccScheduleFull
)


SELECT * FROM cteFirstGroup
WHERE row_num = 1
    UNION ALL
SELECT * FROM cteFirstGroup
WHERE row_num = 2

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here is a crosstab example that I think is getting the desired results. I used a window function in to be able to get correct rows in case. if you needed the 1 and 2 values to be ordered differently ( I used start time) the order by in the over clause can be adjust to what is necessary.

CREATE TABLE #tmp(
    [Run Date] [datetime] NULL,
    [Student ID] [int] NOT NULL,
    [Unique Course Identifier] [varchar](255) NULL,
    [Course Title] [char](35) NULL,
    [Term] [char](6) NOT NULL,
    [Email] [char](60) NULL,
    [Days] [varchar](20) NULL,
    [Section Number] [varchar](20) NULL,
    [Start Time] [datetime] NULL,
    [End Time] [datetime] NULL,
    [Start Date] [datetime] NULL,
    [End Date] [datetime] NULL,
    [Building] [varchar](20) NULL,
    [Room] [varchar](20) NULL,
    [Days2] [varchar](20) NULL,
    [Start Time2] [datetime] NULL,
    [End Time2] [datetime] NULL,
    [Building2] [varchar](20) NULL,
    [Room2] [varchar](20) NULL,
    [Enrolled] [char](1) NULL,
    [Enrollment End Date] [datetime] NULL,
    [SCH_ORIGINAL_REG_TIM] [datetime] NULL,
    [SCH_ADD_DTE] [datetime] NULL,
    [SCH_DROP_DTE] [datetime] NULL,
    [SM_YR_CDE] [char](6) NULL,
    [SM_TRM_CDE] [char](6) NULL,
    [YT_TRM_BEGIN_DTE] [datetime] NULL,
    [YT_TRM_END_DTE] [datetime] NULL,
    [SCH_BEGIN_DTE] [datetime] NULL,
    [SCH_END_DTE] [datetime] NULL,
    [SS_BEGIN_DTE] [datetime] NULL,
    [SS_END_DTE] [datetime] NULL
)



insert into #tmp([Student ID],[Unique Course Identifier],[Course Title],Term,[Section Number],Days,[Start Time],[End Time],[Start Date],[End Date],Building,Room)
values 
(119527,'BASC 5101 001 WINTER 2022_202202_001','BIOLOGY OF CELLS AND TISSUE',202202,1,'T','10:00:00','11:50:00','5/2/2022','8/12/2022','EAST','E110'),
(119527,'BASC 5101 001 WINTER 2022_202202_001','BIOLOGY OF CELLS AND TISSUE',202202,1,'W','9:00:00','10:50:00','5/2/2022','8/12/2022','ONLN','ONLN'),
(1345152,'CHSC 7402 001 FALL 2022_202201_001','SOT',202201,1,'M','9:01:00','9:59:00','5/2/2022','8/12/2022','ONLN','ONLN'),
(1564136,'AMM  0102 002 FALL 2022_202201_0102','Pathology for Massage Professional',202201,102,'TRS','6:00:00 PM','10:00:00 PM','3/10/2022','4/12/2022','CHS','121'),
(1564136,'HHM  0101 002 FALL 2022_202201_0101','Human Health & Hygiene',202201,101,'MWF','6:00:00 PM','10:00:00 PM','4/4/2022','4/13/2022','ONLN','ONLN'),
(2164136,'HHM  0102 002 FALL 2022_202201_0102','Nutrition',202201,102,'MWF','12:00:00 AM','12:00:00 PM','4/11/2022','4/13/2022','ONLN','ONLN'),
(2164136,'HHM  0102 002 FALL 2022_202201_0102','Nutrition',202201,102,'R','6:00:00 PM','10:00:00 PM','4/14/2022','4/14/2022','ONLN','ONLN'),
(2365362,'CHSC 7402 001 WINTER 2022_202202_001','SOT',202202,1,'M','9:01:00 PM','9:59:00 PM','5/2/2022','8/12/2022','ONLN','ONLN'),
(2365362,'CHSC 7409 001 WINTER 2022_202203_001','ACTIVATOR I TECHNIQUE',202203,1,'T','9:01:00 PM','9:59:00 PM','5/2/2022','8/12/2022','ONLN','ONLN'),
(9999935,'BASC 5101 001 FALL 2022_202201_001','BIOLOGY OF CELLS AND TISSUE',202201,1,'T','10:00:00 AM','11:50:00 AM','5/2/2022','8/12/2022','EAST','E110'),
(9999935,'BASC 5101 001 FALL 2022_202201_001','BIOLOGY OF CELLS AND TISSUE',202201,1,'W','9:00:00 AM','10:50:00 AM','5/2/2022','8/12/2022','ONLN','ONLN')



with cte as(
SELECT [Student ID],
       [Unique Course Identifier],
       [Course Title],
       Term,
       [Section Number],
       Days,
       [Start Time],
       [End Time],
       [Start Date],
       [End Date],
       Building,
       Room,
       ROW_NUMBER() OVER(PARTITION BY [Student ID],
                                      [Unique Course Identifier]
       ORDER BY [Start Time]) as rn
FROM #tmp)

SELECT  [Student ID],
       [Unique Course Identifier],
       [Course Title],
       Term,
       [Section Number],
      max(case when rn = 1 then [Days] end) as days1,
      max(case when rn = 1 then [Start Time] end )as [Start time1],
      max(case when rn = 1 then [End Time] end ) as [End Time1],
      max(case when rn = 1 then [Start Date] end) as [Start Date1],
      max(case when rn = 1 then [End Date] end) as [End Date1],
      max(case when rn = 1 then Building end )as Building1,
      max(case when rn = 1 then Room end )as room1,
      max(case when rn = 2 then [Days] end) as days2,
      max(case when rn = 2 then [Start Time] end )as [Start time2],
      max(case when rn = 2 then [End Time] end ) as [End Time2],
      max(case when rn = 2 then [Start Date] end) as [Start Date2],
      max(case when rn = 2 then [End Date] end) as [End Date2],
      max(case when rn = 2 then Building end )as Building2,
      max(case when rn = 2 then Room end )as room2
FROM    cte
group by [Student ID],
       [Unique Course Identifier],
       [Course Title],
       Term,
       [Section Number]

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply