SQL creating column for each distinct value

Question

I have a table that looks like this:

 date | title | count
------+-------+-------
04/20 |  foo  |  420
04/20 |  bar  |  194
04/21 |  foo  |  23
04/21 |  bar  |  100

I want to get a result like this:

 date | foo | bar 
------+-----+-----
04/20 | 420 | 194 
04/21 | 23  | 100

How can I do this in Redshift SQL? Thanks!

possible duplicate of [PostgreSQL Crosstab Query](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query) — jpw, Jun 24 '15 at 17:20
You shouldn't used protected words for field names (date and count) — Tingo, Jun 24 '15 at 17:35

score 2 · Accepted Answer · answered Jun 24 '15 at 17:27

2

I would think the simplest way to do it would be to use case when with group by like so:

SELECT "date",
       SUM(CASE WHEN title = 'foo' THEN "count" END) AS foo,
       SUM(CASE WHEN title = 'bar' THEN "count" END) AS bar
FROM yourTable
GROUP BY "date"
ORDER BY "date"

answered Jun 24 '15 at 17:27

Stephan

5,891
1
16
24

1

Thanks! I'm probably just going to dynamically generate the query using ERB, since I have a lot of columns. – Ian Macalinao Jun 24 '15 at 17:38

SQL creating column for each distinct value

1 Answers1