CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstabunpivot
toink
toink

Reputation: 255

postgres crosstab / transpose

I would like to ask for help.. I have this table

user_id  | Metric_1 | Metric_2 | Metric_3
------------------------------------------
1        | Jan      | 10       | yes
2        | Feb      | 10       | No
3        | Mar      | 20       | No
4        | Apr      | 30       | No
5        | May      | 40       | yes
6        | Jun      | 50       | No
7        | Jul      | 60       | No
8        | Aug      | 70       | yes
9        | Sep      | 80       | No
10       | Oct      | 90       | yes
11       | Nov      | 10       | No
12       | Dec      | 20       | No

I would like to get this result in a query, I'm using postgres tablefunc crosstab but I can get it to have this result... hope you can help me

| January | February | March | April | May
 ---------------------------------------------------
| 10      | 10       | 20    |30     |40
| yes     | NO       | No    |yes    |No

Upvotes: 0

Views: 594

Answers (1)

Taryn
Taryn

Reputation: 247640

For this type of transformation you can unpivot and then pivot the data. For the unpivot, you can use a UNION ALL query and then apply an aggregate function with a CASE expression to pivot the data:

select 
  max(case when metric_1 ='Jan' then value end) as Jan,
  max(case when metric_1 ='Feb' then value end) as Feb,
  max(case when metric_1 ='Mar' then value end) as Mar,
  max(case when metric_1 ='Apr' then value end) as Apr,
  max(case when metric_1 ='May' then value end) as May,
  max(case when metric_1 ='Jun' then value end) as Jun,
  max(case when metric_1 ='Jul' then value end) as Jul,
  max(case when metric_1 ='Aug' then value end) as Aug,
  max(case when metric_1 ='Sep' then value end) as Sep,
  max(case when metric_1 ='Oct' then value end) as Oct,
  max(case when metric_1 ='Nov' then value end) as Nov,
  max(case when metric_1 ='Dec' then value end) as "Dec"
from
(
  select user_id, metric_1, cast(metric_2 as varchar(10)) as value, 'Metric2' as col
  from yourtable
  union all
  select user_id, metric_1, metric_3 as value, 'Metric3' as col
  from yourtable
) src
group by col
order by col

See SQL Fiddle with Demo

The result is:

| JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC |
-------------------------------------------------------------------------
|  10 |  10 |  20 |  30 |  40 |  50 |  60 |  70 |  80 |  90 |  10 |  20 |
| yes |  No |  No |  No | yes |  No |  No | yes |  No | yes |  No |  No |

Upvotes: 2

Related Questions