Transpose table by Row number

Question

Post reply

Transpose table by Row number

scott@lucsam.com

SSC Veteran

Points: 221
More actions
February 21, 2024 at 11:12 am

#4382250
I want to transpose the attached table of data so that all the data for the same "rownumber" appear on one row by session number, preferably in separate columns as opposed to using the STUFF function. Also it would need to be in a grid fashion, meaning missing session numbers would need to be accounted for.
So essentially it would be a table by rownumber with each session number and other data columns going across for each session associated with that rownumber.
- This topic was modified 9 months ago by scott@lucsam.com.
Attachments:
You must be logged in to view attached files.

February 21, 2024 at 11:42 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Presumably you want to do this in a SQL query? Or is this an Excel question?

Can you add a sheet to the spreadsheet showing how you would like the results to look?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BobbyTables SSC-Addicted Points: 493 More actions · Answer 2

i convrted your table data into a CTE so we had code to test with.

I was thinking using a dense rank would get you your one row)number per group, but the source of all sessions was not included. my output here shows that i was expecting sessions 1-12, but my data ended at 8.

is that what you are after? did i misunderstand the requirement?

;WITH MyCTE([sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
AS
(
SELECT '1','8','1','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 1' UNION ALL
SELECT '1','8','2','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 2' UNION ALL
SELECT '1','8','3','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 3' UNION ALL
SELECT '1','8','4','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 4' UNION ALL
SELECT '1','8','5','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 5' UNION ALL
SELECT '1','8','6','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 6' UNION ALL
SELECT '1','8','7','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 7' UNION ALL
SELECT '1','8','8','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 8' UNION ALL
SELECT '1','8','9','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 9' UNION ALL
SELECT '1','8','10','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 10' UNION ALL
SELECT '1','8','11','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 11' UNION ALL
SELECT '1','8','12','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 12' UNION ALL
SELECT '1','8','13','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 13' UNION ALL
SELECT '1','8','14','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 14' UNION ALL
SELECT '1','8','15','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 15' UNION ALL
SELECT '1','8','16','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 16' UNION ALL
SELECT '1','8','17','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 17' UNION ALL
SELECT '2','8','1','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 18' UNION ALL
SELECT '2','8','2','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 19' UNION ALL
SELECT '2','8','3','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 20' UNION ALL
SELECT '2','8','4','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 21' UNION ALL
SELECT '2','8','5','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 22' UNION ALL
SELECT '2','8','6','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 23' UNION ALL
SELECT '2','8','7','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 24' UNION ALL
SELECT '2','8','8','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 25' UNION ALL
SELECT '2','8','9','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 26' UNION ALL
SELECT '2','8','10','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 27' UNION ALL
SELECT '2','8','11','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 28' UNION ALL
SELECT '2','8','12','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 29' UNION ALL
SELECT '2','8','13','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 30' UNION ALL
SELECT '2','8','14','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 31' UNION ALL
SELECT '2','8','15','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 32' UNION ALL
SELECT '3','8','1','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 33' UNION ALL
SELECT '3','8','2','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 34' UNION ALL
SELECT '3','8','3','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 35' UNION ALL
SELECT '3','8','4','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 36' UNION ALL
SELECT '3','8','5','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 37' UNION ALL
SELECT '3','8','6','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 38' UNION ALL
SELECT '3','8','7','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 39' UNION ALL
SELECT '3','8','8','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 40' UNION ALL
SELECT '3','8','9','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 41' UNION ALL
SELECT '3','8','10','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 42' UNION ALL
SELECT '3','8','11','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 43' UNION ALL
SELECT '3','8','12','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 44' UNION ALL
SELECT '3','8','13','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 45' UNION ALL
SELECT '3','8','14','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 46' UNION ALL
SELECT '3','8','15','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 47' UNION ALL
SELECT '3','8','16','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 48' UNION ALL
SELECT '3','8','17','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 49' UNION ALL
SELECT '4','8','1','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 50' UNION ALL
SELECT '4','8','2','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 51' UNION ALL
SELECT '4','8','3','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 52' UNION ALL
SELECT '4','8','4','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 53' UNION ALL
SELECT '4','8','5','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 54' UNION ALL
SELECT '4','8','6','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 55' UNION ALL
SELECT '4','8','7','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 56' UNION ALL
SELECT '4','8','8','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 57' UNION ALL
SELECT '4','8','9','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 58' UNION ALL
SELECT '4','8','10','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 59' UNION ALL
SELECT '4','8','11','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 60' UNION ALL
SELECT '4','8','12','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 61' UNION ALL
SELECT '4','8','13','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 62' UNION ALL
SELECT '4','8','14','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 63' UNION ALL
SELECT '4','8','15','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 64' UNION ALL
SELECT '4','8','16','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 65' UNION ALL
SELECT '4','8','17','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 66' UNION ALL
SELECT '5','8','1','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 67' UNION ALL
SELECT '5','8','2','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 68' UNION ALL
SELECT '5','8','3','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 69' UNION ALL
SELECT '5','8','4','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 70' UNION ALL
SELECT '5','8','5','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 71' UNION ALL
SELECT '5','8','6','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 72' UNION ALL
SELECT '5','8','7','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 73' UNION ALL
SELECT '5','8','8','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 74' UNION ALL
SELECT '5','8','9','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 75' UNION ALL
SELECT '5','8','10','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 76' UNION ALL
SELECT '5','8','11','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 77' UNION ALL
SELECT '5','8','12','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 78' UNION ALL
SELECT '5','8','13','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 79' UNION ALL
SELECT '5','8','14','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 80' UNION ALL
SELECT '5','8','15','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 81' UNION ALL
SELECT '5','8','16','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 82' UNION ALL
SELECT '5','8','17','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 83' UNION ALL
SELECT '5','8','18','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 84' UNION ALL
SELECT '6','8','1','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 85' UNION ALL
SELECT '6','8','2','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 86' UNION ALL
SELECT '6','8','3','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 87' UNION ALL
SELECT '6','8','4','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 88' UNION ALL
SELECT '6','8','5','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 89' UNION ALL
SELECT '6','8','6','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 90' UNION ALL
SELECT '6','8','7','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 91' UNION ALL
SELECT '6','8','8','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 92' UNION ALL
SELECT '6','8','9','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 93' UNION ALL
SELECT '6','8','10','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 94' UNION ALL
SELECT '6','8','11','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 95' UNION ALL
SELECT '6','8','12','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 96' UNION ALL
SELECT '6','8','13','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 97' UNION ALL
SELECT '6','8','14','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 98' UNION ALL
SELECT '6','8','15','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 99' UNION ALL
SELECT '6','8','16','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 100' UNION ALL
SELECT '7','8','1','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 101' UNION ALL
SELECT '7','8','2','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 102' UNION ALL
SELECT '7','8','3','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 103' UNION ALL
SELECT '7','8','4','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 104' UNION ALL
SELECT '7','8','5','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 105' UNION ALL
SELECT '7','8','6','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 106' UNION ALL
SELECT '7','8','7','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 107' UNION ALL
SELECT '7','8','8','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 108' UNION ALL
SELECT '7','8','9','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 109' UNION ALL
SELECT '7','8','10','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 110' UNION ALL
SELECT '7','8','11','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 111' UNION ALL
SELECT '7','8','12','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 112' UNION ALL
SELECT '7','8','13','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 113' UNION ALL
SELECT '7','8','14','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 114' UNION ALL
SELECT '7','8','15','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 115' UNION ALL
SELECT '7','8','16','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 116' UNION ALL
SELECT '7','8','17','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 117' UNION ALL
SELECT '7','8','18','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 118' UNION ALL
SELECT '7','8','19','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 119' UNION ALL
SELECT '8','8','1','15-Sep-23','Friday','15-Sep-23','Friday','9:30 AM','11:30 AM','Name 120' 
),
--this CTE represents some table with all possible sessions to identify gaps or missing ranges
PossibleRange 
AS
(
SELECT TOP 12 ROW_NUMBER() OVER(ORDER BY object_id) AS basesession FROM sys.objects
)

SELECT dense_rank() over (ORDER BY basesession) AS Rw, 
PossibleRange.* ,
MyCTE.*
--INTO #AttachedTable 
FROM PossibleRange
LEFT JOIN MyCTE ON PossibleRange.basesession = MyCTE.sessionnumber

scott@lucsam.com SSC Veteran Points: 221 More actions · Answer 3

scott@lucsam.com

SSC Veteran

Points: 221

February 21, 2024 at 11:41 am

#4382261

Hi yes SQL

I can transpose it manually in another sheet

scott@lucsam.com SSC Veteran Points: 221 More actions · Answer 4

Hi there are in this example a max number of sessions of 8. There are multiple rows per session. So I am looking for each row to be the row number and each session across with the other data, dates, times and name per row per session.

in your example it appears all the data has been hard coded or is that the output

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Set up data

drop table if exists #a
go
;WITH MyCTE([sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
AS
(
SELECT '1','8','1','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 1' UNION ALL
SELECT '1','8','2','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 2' UNION ALL
SELECT '1','8','3','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 3' UNION ALL
SELECT '1','8','4','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 4' UNION ALL
SELECT '1','8','5','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 5' UNION ALL
SELECT '1','8','6','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 6' UNION ALL
SELECT '1','8','7','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 7' UNION ALL
SELECT '1','8','8','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 8' UNION ALL
SELECT '1','8','9','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 9' UNION ALL
SELECT '1','8','10','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 10' UNION ALL
SELECT '1','8','11','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 11' UNION ALL
SELECT '1','8','12','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 12' UNION ALL
SELECT '1','8','13','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 13' UNION ALL
SELECT '1','8','14','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 14' UNION ALL
SELECT '1','8','15','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 15' UNION ALL
SELECT '1','8','16','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 16' UNION ALL
SELECT '1','8','17','7-Apr-24','Sunday','8-Apr-24','Monday','10:00 PM','1:00 AM','Name 17' UNION ALL
SELECT '2','8','1','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 18' UNION ALL
SELECT '2','8','2','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 19' UNION ALL
SELECT '2','8','3','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 20' UNION ALL
SELECT '2','8','4','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 21' UNION ALL
SELECT '2','8','5','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 22' UNION ALL
SELECT '2','8','6','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 23' UNION ALL
SELECT '2','8','7','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 24' UNION ALL
SELECT '2','8','8','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 25' UNION ALL
SELECT '2','8','9','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 26' UNION ALL
SELECT '2','8','10','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 27' UNION ALL
SELECT '2','8','11','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 28' UNION ALL
SELECT '2','8','12','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 29' UNION ALL
SELECT '2','8','13','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 30' UNION ALL
SELECT '2','8','14','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 31' UNION ALL
SELECT '2','8','15','8-Apr-24','Monday','8-Apr-24','Monday','4:30 AM','6:30 AM','Name 32' UNION ALL
SELECT '3','8','1','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 33' UNION ALL
SELECT '3','8','2','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 34' UNION ALL
SELECT '3','8','3','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 35' UNION ALL
SELECT '3','8','4','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 36' UNION ALL
SELECT '3','8','5','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 37' UNION ALL
SELECT '3','8','6','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 38' UNION ALL
SELECT '3','8','7','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 39' UNION ALL
SELECT '3','8','8','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 40' UNION ALL
SELECT '3','8','9','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 41' UNION ALL
SELECT '3','8','10','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 42' UNION ALL
SELECT '3','8','11','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 43' UNION ALL
SELECT '3','8','12','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 44' UNION ALL
SELECT '3','8','13','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 45' UNION ALL
SELECT '3','8','14','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 46' UNION ALL
SELECT '3','8','15','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 47' UNION ALL
SELECT '3','8','16','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 48' UNION ALL
SELECT '3','8','17','8-Apr-24','Monday','9-Apr-24','Tuesday','10:00 PM','1:00 AM','Name 49' UNION ALL
SELECT '4','8','1','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 50' UNION ALL
SELECT '4','8','2','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 51' UNION ALL
SELECT '4','8','3','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 52' UNION ALL
SELECT '4','8','4','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 53' UNION ALL
SELECT '4','8','5','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 54' UNION ALL
SELECT '4','8','6','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 55' UNION ALL
SELECT '4','8','7','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 56' UNION ALL
SELECT '4','8','8','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 57' UNION ALL
SELECT '4','8','9','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 58' UNION ALL
SELECT '4','8','10','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 59' UNION ALL
SELECT '4','8','11','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 60' UNION ALL
SELECT '4','8','12','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 61' UNION ALL
SELECT '4','8','13','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 62' UNION ALL
SELECT '4','8','14','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 63' UNION ALL
SELECT '4','8','15','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 64' UNION ALL
SELECT '4','8','16','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 65' UNION ALL
SELECT '4','8','17','9-Apr-24','Tuesday','9-Apr-24','Tuesday','4:30 AM','6:30 AM','Name 66' UNION ALL
SELECT '5','8','1','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 67' UNION ALL
SELECT '5','8','2','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 68' UNION ALL
SELECT '5','8','3','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 69' UNION ALL
SELECT '5','8','4','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 70' UNION ALL
SELECT '5','8','5','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 71' UNION ALL
SELECT '5','8','6','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 72' UNION ALL
SELECT '5','8','7','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 73' UNION ALL
SELECT '5','8','8','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 74' UNION ALL
SELECT '5','8','9','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 75' UNION ALL
SELECT '5','8','10','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 76' UNION ALL
SELECT '5','8','11','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 77' UNION ALL
SELECT '5','8','12','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 78' UNION ALL
SELECT '5','8','13','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 79' UNION ALL
SELECT '5','8','14','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 80' UNION ALL
SELECT '5','8','15','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 81' UNION ALL
SELECT '5','8','16','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 82' UNION ALL
SELECT '5','8','17','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 83' UNION ALL
SELECT '5','8','18','9-Apr-24','Tuesday','10-Apr-24','Wednesday','10:00 PM','1:00 AM','Name 84' UNION ALL
SELECT '6','8','1','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 85' UNION ALL
SELECT '6','8','2','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 86' UNION ALL
SELECT '6','8','3','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 87' UNION ALL
SELECT '6','8','4','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 88' UNION ALL
SELECT '6','8','5','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 89' UNION ALL
SELECT '6','8','6','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 90' UNION ALL
SELECT '6','8','7','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 91' UNION ALL
SELECT '6','8','8','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 92' UNION ALL
SELECT '6','8','9','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 93' UNION ALL
SELECT '6','8','10','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 94' UNION ALL
SELECT '6','8','11','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 95' UNION ALL
SELECT '6','8','12','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 96' UNION ALL
SELECT '6','8','13','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 97' UNION ALL
SELECT '6','8','14','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 98' UNION ALL
SELECT '6','8','15','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 99' UNION ALL
SELECT '6','8','16','10-Apr-24','Wednesday','10-Apr-24','Wednesday','4:30 AM','6:00 AM','Name 100' UNION ALL
SELECT '7','8','1','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 101' UNION ALL
SELECT '7','8','2','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 102' UNION ALL
SELECT '7','8','3','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 103' UNION ALL
SELECT '7','8','4','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 104' UNION ALL
SELECT '7','8','5','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 105' UNION ALL
SELECT '7','8','6','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 106' UNION ALL
SELECT '7','8','7','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 107' UNION ALL
SELECT '7','8','8','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 108' UNION ALL
SELECT '7','8','9','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 109' UNION ALL
SELECT '7','8','10','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 110' UNION ALL
SELECT '7','8','11','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 111' UNION ALL
SELECT '7','8','12','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 112' UNION ALL
SELECT '7','8','13','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 113' UNION ALL
SELECT '7','8','14','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 114' UNION ALL
SELECT '7','8','15','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 115' UNION ALL
SELECT '7','8','16','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 116' UNION ALL
SELECT '7','8','17','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 117' UNION ALL
SELECT '7','8','18','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 118' UNION ALL
SELECT '7','8','19','10-Apr-24','Wednesday','11-Apr-24','Thursday','10:00 PM','1:00 AM','Name 119' UNION ALL
SELECT '8','8','1','15-Sep-23','Friday','15-Sep-23','Friday','9:30 AM','11:30 AM','Name 120' 
)
select *
into #a
from MyCTE

Select query

select rownumber,
       max(case sessionnumber
           when 1 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session1,
       max(case sessionnumber
           when 2 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session2,
       max(case sessionnumber
           when 3 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session3,
       max(case sessionnumber
           when 4 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session4,
       max(case sessionnumber
           when 5 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session5,
       max(case sessionnumber
           when 6 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session6,
       max(case sessionnumber
           when 7 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session7,
       max(case sessionnumber
           when 8 then concat_ws('|',[sessionnumber],[maxsession],[rownumber],[startdate],[startweekday],[enddate],[endweekday],[starttime],[endtime],[NomineeName])
       end) session8
from #a
group by rownumber
order by convert(int,rownumber)
;

scott@lucsam.com SSC Veteran Points: 221 More actions · Answer 6

scott@lucsam.com

SSC Veteran

Points: 221

February 22, 2024 at 1:49 am

#4382627

Thanks that worked for me!

scott@lucsam.com SSC Veteran Points: 221 More actions · Answer 7

Thank you for that it has worked well. I have one more question though, how do I sort this result table by say the name or any of the fields that are used to make up the data, as I cannot use an order by in the view to create this data

This reply was modified 9 months ago by scott@lucsam.com.

Transpose table by Row number

Attachments: