Convert rows to column

Question

Convert rows to column

Barcelona10

SSCertifiable

Points: 5372
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: 63503 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: 5372 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: 26952 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 years, 7 months 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: 246964 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;

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

Barcelona10 SSCertifiable Points: 5372 More actions · Answer 5

Barcelona10

SSCertifiable

Points: 5372

April 12, 2022 at 2:21 pm

#4014574

Thank you MARK/PHIL

Now it works

Thank you very much