Transpose table by Row number

  • 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.

     

    Attachments:
    You must be logged in to view attached files.
  • 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

  • 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

     

  • Hi yes SQL

    I can transpose it manually in another sheet

  • This was removed by the editor as SPAM

  • 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

     

  • 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)
    ;

     

  • Thanks that worked for me!

  • 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

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

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