Pivot and Sum in Amazon Redshift

user_6724844 0
Viewed 57

I have a following tables

table1

id name
1  A
3  B

table2

id label   value
1   tag     a
1   tag     b
1   time    10
1   time    20
1   score   20
2   tag     a
2   time    30
2   score   40 
3   tag     b 
3   time    50
3   time    55
3   score   60

first I'd like to join table2 as follows

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

id name tag
1   A   a
1   A   b
3   B   b

and then join table2 with id and pivot and sum up them

id name tag time score
1   A   a   10    20
1   A   b   10    20
3   B   b   50    60

I guess it is very complicated, are there any way to achieve this?

In Redshift it seems that there is no way to pivot them.

Thanks.

amazon-redshift amazon-web-services sql
1 Answers

This looks to be a pivot query. I think this does what you are looking for:

create table table1 (id int, name varchar(16));
insert into table1 values
(1, 'A'),
(3, 'B')
;

create table table2 (id int, label varchar(16), value varchar(16));
insert into table2 values 
(1,   'tag', 'a'),
(1,   'tag', 'b'),
(1,   'time', '10'),
(1,   'score', '20'),
(2,   'tag', 'a'),
(2,   'time', '30'),
(2,   'score', '40'),
(3,   'tag', 'b'), 
(3,   'time', '50'),
(3,   'score', '60')
;

select t2.id, a.name, a.tag_value, sum(decode(label, 'time', value::int)) as total_time, sum(decode(label, 'score', value::int)) as total_score
from table2 t2
join (
    select id, name, value as tag_value
    from table1 t1 left join table2 t2 using(id)
    where t2.label in ('tag')
    ) a
on t2.id = a.id 
group by 1, 2, 3
order by 1, 2, 3
;
user_133506520
Related