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. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 16, 2013 at 23:20

Taryn · Accepted Answer · 2013-01-16 21:16:31Z

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

Peter Wooster · Accepted Answer · 2013-01-16 21:03:28Z

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, 2013 at 21:03

Peter Wooster

6,0882 gold badges30 silver badges39 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:48:59Z

3

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.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jan 16, 2013 at 23:33

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

storm_buster · Accepted Answer · 2013-01-16 21:13:47Z

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, 2013 at 21:13

answered Jan 16, 2013 at 21:08

storm_buster

7,56818 gold badges56 silver badges76 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related