0

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
Improve this question
1
  • 1
    you will get much better help if you provide table schema, sample data, what you have attempted so far, and desired result. you mentioned pivot which might work or you can lookup crosstab queries, which is similar to pivot but with more common syntax
    – Bob Klimes
    Commented Mar 10, 2022 at 17:18

1 Answer 1

Reset to default
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]
Improve this answer
1
  • Thanks, that looks very excellent!
    – ERPISE
    Commented Mar 10, 2022 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.