The question:
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
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
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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]
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