CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
hangc
hangc

Reputation: 5473

Redshift Product of Column Values

How can I generate all possible combination of multiple column in redshift?

For example for all combinations of name and group,

| name  | group | value |
+-------+-------+-------|
| a     | 1     | 1     |
| a     | 2     | 1     |
| a     | 3     | 1     |
| b     | 1     | 1     |
| b     | 2     | 1     |
| c     | 3     | 1     |

I want to obtain the following:

| name  | group | value |
+-------+-------+-------|
| a     | 1     | 1     |
| a     | 2     | 1     |
| a     | 3     | 1     |
| b     | 1     | 1     |
| b     | 2     | 1     |
| b     | 3     | Null  |
| c     | 1     | Null  |
| c     | 2     | Null  |
| c     | 3     | 1     |

Upvotes: 0

Views: 369

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this using cross join and left join:

select n.name, g.group, t.value
from (select distinct name from t) n cross join
     (select distinct group from t) g left join
     t
     on t.name = n.name and t.group = g.group
order by 1, 2;

You don't have to use a subquery to get the distinct values if you have them in another table.

Upvotes: 1

Related Questions