SQL Join to put rows into columns [duplicate]

Question

This question already has answers here:

How to Pivot in PostgreSQL (3 answers)

Closed 5 years ago.

I have two Tables

TABLE 1

ID Name
1001 XYZ
1002 ABC

TABLE 2

ID GAME 
1001 A 
1001 B 
1002 A 

I want the outcome of two tables join as

ID NAME GAME1 GAME2 
1001 XYZ A B 
1002 ABC A 

I tried with a simple join and it gives me multiple rows for 1001 and 1002

Query

SELECT t1.ID, t1.Name, t2.Game
FROM t1, t2
WHERE t1.ID = t2.ID

1001 A
1001 B
1002 A
NA

Actual O/P

1001 A
1001 B
1002 A

Expected O/P

ID NAME GAME1 GAME2 
1001 XYZ A B 
1002 ABC A

Answer 1

Here is one of the ways to do it!

First you need to convert the t2 table as columns provided you are certain about the values of the game.

SELECT
 max(CASE WHEN game = 'A' THEN game END) GAME1,
 max(CASE WHEN game = 'B' THEN game END) GAME2,
 ID 
FROM T2 GROUP BY ID

This will give you something like

 game1 game2 ID
 A B 1001
 A NULL 1002

Now join this with your t1.

SELECT t1.id, t1.name, S1.GAME1, COALESCE(S1.GAME2, '') FROM t1
 JOIN (SELECT
 max(CASE WHEN game = 'A' THEN game END) GAME1,
 max(CASE when game= 'B' THEN game END) GAME2,
 ID FROM T2 GROUP BY ID) S1 
 ON t1.id = S1.ID

Keep in mind that this will be more lengthy when the game types increase.

• Rakesh: Thank you for your response the First Query does not work the way you mentioned It gave me a result like game1 game2 ID A NULL 1001