CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
dbz
dbz

Reputation: 421

PostgreSQL how to convert rows to colums

I have a table like this in my postgres BD:

CREATE TABLE foo(id,type,date,value) AS
SELECT *
FROM ( VALUES
  (11::smallint,'A','2016-06-06 19:00:00'::timestamp,81),
  (11,'A','2016-06-06 20:00:00',70),
  (11,'A','2016-06-06 21:00:00',35),
  (11,'B','2016-06-06 19:00:00',2),
  (11,'B','2016-06-06 20:00:00',0),
  (11,'B','2016-06-06 21:00:00',0)
) as f;

Here is the data,

 id | type |        date         | value
----+------+---------------------+------
 11 | A    | 2016-06-06 19:00:00 |    81
 11 | A    | 2016-06-06 20:00:00 |    70
 11 | A    | 2016-06-06 21:00:00 |    35
 11 | B    | 2016-06-06 19:00:00 |     2
 11 | B    | 2016-06-06 20:00:00 |     0
 11 | B    | 2016-06-06 21:00:00 |     0

And this is the result that i want to get:

 ID           DATE            A   B
-----|----------------------|----|---
 11  | 2016-06-06 19:00:000 | 81 | 2
 11  | 2016-06-06 20:00:000 | 70 | 0
 11  | 2016-06-06 21:00:000 | 35 | 0

Any one knows what is the query that i have to make to get this result?

Upvotes: 1

Views: 169

Answers (2)

Rémy Baron
Rémy Baron

Reputation: 1399

Sagi's solution is good , to use a lot of types You can create a request that generates the request of sagi :

 select $$SELECT t.id,t.date,$$
       ||string_agg(distinct $$MAX(CASE WHEN t.type = '$$||"type"||$$' THEN t.value END) as $$||"type"||$$_col$$,',')
       ||$$ FROM YourTable t GROUP BY t.id,t.date$$ 
   from YourTable

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use conditional aggregation : 

SELECT t.id,t.date,
       MAX(CASE WHEN t.type = 'A' THEN t.value END) as a_col,
       MAX(CASE WHEN t.type = 'B' THEN t.value END) as b_col
FROM YourTable t
GROUP BY t.id,t.date

Upvotes: 2

Related Questions