SQL Select to combine row values in output table using Pivot or CASE

Question

I am using a PostgreSQL database and have a table as follows:

--------------------------------------
| Date  | MetricType  | MetricValue  |
--------------------------------------
| date1 | MetricA     | val          |
--------------------------------------
| date1 | MetricB     | val          |
--------------------------------------
| date1 | MetricC     | val          |
--------------------------------------
| date2 | MetricA     | val          |
--------------------------------------
| date2 | MetricB     | val          |
--------------------------------------
| date2 | MetricC     | val          |
--------------------------------------

As you can see, each date has a set of metric types and each type has a value. I want to write a Select statement which combines this data in the following manor

------------------------------------------
| Date  | MetricA  | MetricB  | MetricC  | 
------------------------------------------
| date1 | val      | val      | val      |
------------------------------------------
| date2 | val      | val      | val      |
------------------------------------------

I am not sure how to go about writing this Select statement to get these results. Can anyone help?

As *always* the question should include your version of PostgreSQL and an exact table definition. Consider the [info for the PostgreSQL tag](http://stackoverflow.com/tags/postgresql/info). — Erwin Brandstetter, Jan 16 '13 at 23:20

score 3 · Answer 1 · answered Jan 16 '13 at 21:03

3

See http://www.artfulsoftware.com/infotree/queries.php#78

For a tutorial, what you are looking for is called a "pivot" This can also be done using CASE as shown here:http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

answered Jan 16 '13 at 21:03

Peter Wooster

6,009
2
27
39

Taryn · Accepted Answer · 2013-01-16T21:16:31.027

This data transformation is a pivot. If your database doesn't have a pivot function, then you can use an aggregate function with a CASE expression:

select Date,
  max(case when MetricType = 'MetricA' then MetricValue end) MetricA,
  max(case when MetricType = 'MetricB' then MetricValue end) MetricB,
  max(case when MetricType = 'MetricC' then MetricValue end) MetricC
from yourtable
group by Date

See SQL Fiddle with Demo

The result is:

|  DATE | METRICA | METRICB | METRICC |
---------------------------------------
| date1 |     val |     val |     val |
| date2 |     val |     val |     val |

You can also do this using multiple joins on the table:

select a.date as Date,
 a.MetricValue as MetricA,
 b.MetricValue as MetricB,
 c.MetricValue as MetricC 
from yourtable a
left join yourtable b 
  on a.date = b.date and b.MetricType = 'MetricB'
left join yourtable c 
  on a.date = c.date and c.MetricType = 'MetricC'
where a.MetricType = 'MetricA'

See SQL Fiddle with Demo

score 3 · Answer 3 · edited May 23 '17 at 11:48

Actually, Postgres does have pivot functions. Install the additional module tablefunc.
Find more details and explanation under this related question:
PostgreSQL Crosstab Query

Your query could look like this. Assuming the metrics to be of data type integer:

SELECT * FROM crosstab(
   'SELECT date, metrictype, metricvalue 
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

   ,$$VALUES ('MetricA'::text), ('MetricB'), ('MetricC')$$)
AS ct ("Section" text, "MetricA" int, "MetricB" int, "MetricC" int);

For simple queries a CASE statement like demonstrated by @Bluefeet will do. But this query performs much faster and is much shorter for longer lists of columns / bigger tables.

storm_buster · Answer 4 · 2013-01-16T21:13:47.087

0

    select a.date as Date,
     a.metric as MetricType ,
b.metric as MetricType ,
     c.metric as MetricType 
    from (select distinct date from yourtable where metric = 'metricA')  a
    join yourtable b on a.date = b.date and b.metric = 'metricB'
    join yourtable c on a.date = c.date and c.metric = 'metricC'

edited Jan 16 '13 at 21:13

answered Jan 16 '13 at 21:08

storm_buster

7,362
18
53
75

SQL Select to combine row values in output table using Pivot or CASE

4 Answers4