CODEWITHSUNDEEP
sqlpostgresqljoin
Santosh Anantharamaiah
Santosh Anantharamaiah

Reputation: 273

Joining multiple select queries on same table PostgreSql

Below is the sample table structure what I have got,

C1  C2  C3 C4
A   D   G  X
B   E   H  X
C   F   I  X

select C2 as 1_C2, C3 as 1_C3 from table1 where C1 = A and C4=X
select C2 as 2_C2, C3 as 2_C3 from table1 where C1 = B and C4=X
select C2 as 3_C2, C3 as 3_C3 from table1 where C1 = C and C4=X

Above are the three select statements what I have got. Now I need to join all three select statements and get just one row as the output like,

1_C2 2_C2 3_C2 1_C3 2_C3 3_C3    
   D    E    F    G    H    I

Saw multiple other posts but didn't match this requirement. Any help is highly appreciated.

Upvotes: 1

Views: 761

Answers (1)

Alex Zen
Alex Zen

Reputation: 926

You could use a CASE expression, combined with MAX():

select MAX(CASE WHEN C1 = 'A' THEN C2 END) as 1_C2,
       MAX(CASE WHEN C1 = 'B' THEN C2 END) as 2_C2,
       MAX(CASE WHEN C1 = 'C' THEN C2 END) as 3_C2,
       MAX(CASE WHEN C1 = 'A' THEN C3 END) as 1_C3,
       MAX(CASE WHEN C1 = 'B' THEN C3 END) as 2_C3,
       MAX(CASE WHEN C1 = 'C' THEN C3 END) as 3_C3
  from table1
 where C1 in ('A', 'B', 'C')
   and C4 = 'X';

Upvotes: 2

Related Questions