Postgres - Possibly a cross-plan?
I have Table A with 3 columns -
date | type | value
_________________________
2012-01-01 | 1 | 100
2012-01-01 | 2 | 200
2012-01-02 | 1 | 200
2012-01-02 | 2 | 300
2012-01-03 | 1 | 500
2012-01-03 | 2 | 10
Is it possible to get the query result for this data in a format like this -
date | type-1 | type-2
_____________________________________
2012-01-01 100 200
2012-01-02 200 300
It looks like a crosstab issue. Not sure though. any ideas how to write SQL for this?
+3
source to share
1 answer
You can use an aggregate function with an expression CASE
to get the result:
select date,
sum(case when type = 1 then value end) Type1,
sum(case when type = 2 then value end) Type2
from yourtable
group by date
You can also join your table multiple times:
select t1.date,
t1.value Type1,
t2.value Type2
from yourtable t1
left join yourtable t2
on t1.date = t2.date
and t2.type = 2
where t1.type = 1
+4
source to share