CODEWITHSUNDEEP
sqlpostgresqlpivot
Mahdi
Mahdi

Reputation: 191

Reshaping data in SQL

I have a simple table like

time_stamp, metric, value
2012/3/2  , x     , 1
2012/3/2  , y     , 1.6
2012/3/3  , x     , 0
2012/3/3  , y     , null
...

and I want to write a query that returns something like

time_stamp, x, y
2012/3/2  , 1, 1.6
2012/3/3  , 0, null

In other words, basically generate columns based on unique values of one field on the fly and reshape data into it.

I remember I have seen it somewhere, but I just cannot find it now.

PS. I am using PostgreSQL as of now. Please note if the solution is specific to a particular DBMS.

Updates: The values of the metric, from which columns are being formed are dynamic. I understand that makes this harder though and a single query solution almost impossible.

As per comments, I have tried crosstab as well and it is a) very pg specific and b) not so nice with dynamic number of tables.

Upvotes: 1

Views: 1654

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

Use conditional aggregate to transpose the rows.

select time_stamp,
      max(case when metric= 'X' then value End) as X, 
      max(case when metric= 'Y' then value End) as Y, 
From Yourtable
group by time_stamp

Upvotes: 4

Related Questions