SQL Join to put rows into columns [duplicate]

Question

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.