Convert rows to column

Question

Convert rows to column

Barcelona10

SSCertifiable

Points: 5065
More actions
April 12, 2022 at 1:21 am

#4014280

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

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

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

pietlinden SSC Guru Points: 63198 More actions · Answer 1

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

Barcelona10 SSCertifiable Points: 5065 More actions · Answer 2

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' )

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 245820 More actions · Answer 4

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.

Barcelona10 SSCertifiable Points: 5065 More actions · Answer 5

Barcelona10

SSCertifiable

Points: 5065

April 12, 2022 at 2:21 pm

#4014574

Thank you MARK/PHIL

Now it works

Thank you very much