how to join with complecated condition in redshift

Question

I have following table1

id name
1  A
3  B

and table2

id label value
1   a    10
1   b    11
1   c    12
2   a    13
2   b    14
2   c    15 
3   a    16 
3   b    17
3   c    18

My desired result is following.I'd like to join table1 and table2 in label=a and b I guess I must join twice in each condition..

id name a    b
1   A   10   11
3   B   16   17

I tried following one, but I get only result in label = a

select *
from table1
left join table2 using(id)
where label in ('a')

Are there any good way to achieve this? I guess we need transpose

Thanks

score 1 · Accepted Answer · answered Dec 09 '21 at 14:22

Redshift doesn't have a native pivot function so you need to do it using a case statement:

SELECT
    table1.id,
    table1.name,
    SUM(CASE WHEN table2.label = 'a' THEN table2.value END) AS a,
    SUM(CASE WHEN table2.label = 'b' THEN table2.value END) AS b
FROM table1
LEFT JOIN table2
ON table1.id = table2.id
GROUP BY table1.id, table1.name

See this link also: Pivot for redshift database

how to join with complecated condition in redshift

1 Answers1