Convert rows to column

  • Hi

    I have the sample table here

    CREATE TABLE  Game

    (id int,

    Game_Time datetime

    )

    INSERT INTO Game

    VALUES

    (1,'20220101'),

    (1,'20220102' ),

    (2,'20220103'),

    (2,'20220104' )

    SELECT * FROM  Game

    id    Game_Time

    1          2022-01-01

    1         2022-01-02

    2         2022-01-03

    2         2022-01-04

    I need to see results in this form

    id    Game_Start           Game_END

    1     2022-01-01            2022-01-02

    2     2022-01-03           2022-01-04

    Any suggestions?

    Thank you advance

     

     

  • Something like this should work:

    SELECT g.ID,
    StartTime = MAX(IIF(rn=1,Game_time,null)),
    EndTime = MAX(IIF(rn=2,Game_time,null))
    FROM
    (SELECT Game_time, ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Game_Time) AS rn
    FROM Game) g
    GROUP BY g.ID
  • IT works if id has 2 records.

    But what if there are   more than 2 records for let say id=1 ?

    CREATE TABLE  Game

    (id int,

    Game_Time date

    )

    INSERT INTO Game

    VALUES

    (1,'20220101'),

    (1,'20220102' ),

    (1,'20220105'),

    (1,'20220106' ),

    (1,'20220107'),

    (1,'20220108' ),

    (2,'20220103'),

    (2,'20220104' ),

    (2,'20220105'),

    (2,'20220106' )

     

     

  • SELECT id,
    MIN(Game_Time) AS Game_Start,
    MAX(Game_Time) AS Game_END
    FROM Game
    GROUP BY id
    ORDER BY id;

    • This reply was modified 2 months, 2 weeks ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • WITH grouped
    AS
    (SELECT
    g.id
    , g.Game_Time
    , Grp = (ROW_NUMBER() OVER (PARTITION BY g.id ORDER BY g.Game_Time) + 1) / 2
    FROM Game g)
    SELECT
    grouped.id
    , Game_Start = MIN(grouped.Game_Time)
    , Game_End = MAX(grouped.Game_Time)
    FROM grouped
    GROUP BY grouped.id
    , grouped.Grp;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thank you MARK/PHIL

    Now it works

    Thank you very much

Viewing 6 posts - 1 through 5 (of 5 total)

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