T-Sql
將兩行 7 列轉換為一行 14 列
我需要一個 T-SQL 腳本將兩行更改為一行。這些行按學生 ID 和唯一課程標識符分組。天數、開始時間、結束時間、開始日期、結束日期、建築物和房間組中的第二行列需要位於右側列中的第一行，即 Days2、Start Time2、End Time2、Start日期 2、結束日期 2、建築物 2 和房間 2。沒有聚合，所以我似乎不想使用樞軸。看起來它可能與 Union All 或 xml 路徑一起使用。腳本是什麼？SQL Server 2014
表架構
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]樣本數據
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,,,,,,,我試過
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
這是一個交叉表範例，我認為它正在獲得所需的結果。我使用了一個視窗函式來獲得正確的行以防萬一。如果您需要以不同方式對 1 和 2 值進行排序（我使用了開始時間），則 over 子句中的 order by 可以根據需要進行調整。
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]