tsql to pivot data in a partitioned query

Question

I have a SQL query that partitions by 2 columns

select 
ROW_NUMBER() over (partition by CLS.Term_Code,CLS.CRN order by dt.MEETING_TYPE_CODE ) as rowval,
dt.MEETING_TYPE_CODE,CLS.TERM_CODE,
cls.[SUBJECT_CODE] + cls.[COURSE_NUMBER] SEC_COURSE_IDENTIFICATION,
cls.CRN,
loc.BUILDING_CODE as SEC_BUILDING_CODE, 
loc.ROOM_CODE as SEC_RO0M_CODE,
ms.SUNDAY_MEETING_IND as TIM_SUNDAY_IND,
ms.MONDAY_MEETING_IND as TIM_MONDAY_IND,
ms.TUESDAY_MEETING_IND as TIM_TUESDAY_IND,
ms.WEDNESDAY_MEETING_IND as TIM_WEDNESDAY_IND,
ms.THURSDAY_MEETING_IND as TIM_THURSDAY_IND,
ms.FRIDAY_MEETING_IND as TIM_FRIDAY_IND,
ms.SATURDAY_MEETING_IND as TIM_SATURDAY_IND,
ts.TIME_VALUE as TIME_START,
te.TIME_VALUE as TIME_END--,ms.*

from
dbo.F_CLASS_MEETING_TIME mt
join dbo.D_CLASS cls on (cls.CLASS_SID = mt.CLASS_SID)
join dbo.D_MEETING_SCHEDULE ms on (mt.MEETING_SCHEDULE_SID = ms.MEETING_SCHEDULE_SID)
join dbo.D_CAMPUS_LOCATION loc on (mt.CAMPUS_LOCATION_SID = loc.CAMPUS_LOCATION_SID)
join dbo.D_MEETING_DETAIL  dt on (mt.MEETING_DETAIL_SID = dt.MEETING_DETAIL_SID)
join dbo.D_TIME ts on (ts.TIME_SID = mt.START_TIME_SID)
join dbo.D_TIME te on (te.TIME_SID = mt.END_TIME_SID)
order by  CLS.Term_Code,CLS.CRN, dt.MEETING_TYPE_CODE

and it is ragged in that some partitions will be 1 row some 2 ,3,4,...11 etc

what I need to do is select from this

SEC_BUILDING_CODE1,SEC_BUILDING_CODE2,...SEC_BUILDING_CODEn,ROOM_CODE1,ROOM_CODE2,...ROOM_CODEn,TIM_SUNDAY_IND1,TIM_SUNDAY_IND2,...TIM_SUNDAY_INDn

etc

how do I select based on that row number generated by

ROW_NUMBER() over (partition by CLS.Term_Code,CLS.CRN order by dt.MEETING_TYPE_CODE ) as rowval,

No mine is different the values need to be in the same record col1=1000 col2=1100 — R.Merritt, Jan 24 '18 at 15:13
The technique is the same. Without a fixed upper limit you need to use [dynamic SQL](https://docs.microsoft.com/en-us/sql/odbc/reference/dynamic-sql). With a fixed upper limit you can use the [pivot operator](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot) or [conditional aggregation](https://stackoverflow.com/questions/45795898/conditional-aggregation-performance/45797189). Ragged nature of the data isn't an issue. A record with 2 partitions will still have 11 columns in the final result set (9 of them will contain an empty/blank/default/null value). — David Rushton, Jan 24 '18 at 15:21

tsql to pivot data in a partitioned query

0 Answers0