3

I have a 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 result of a query, 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 problem. Not sure though. any ideas how to write an SQL for that?

Taryn
  • 242,637
  • 56
  • 362
  • 405
blue01
  • 2,035
  • 2
  • 23
  • 38

1 Answers1

4

You can use an aggregate function with a CASE expression 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

See SQL Fiddle with Demo

You can also join on 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

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • excellent. I found a way using crosstab. Something like- select * from crosstab('select date::text, type::text, value::numeric(16,2) from tableA where type in (1,2) and date between ''2012-02-06'' and ''2013-02-13'' ORDER by 1,2' ) AS ct(date text,type1 numeric(16,2), type2 numeric(16,2)) but compared the explain analyze and your query is way too fast. always good to learn better queries :) – blue01 Feb 13 '13 at 22:49
  • @Harpreet your way appears to be more dynamic than mine, but there are always different ways to query the data. You can even do this with multiple joins on your table. – Taryn Feb 13 '13 at 23:27