PostgreSQL how to convert rows to colums

Question

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?

sagi · Accepted Answer · 2016-12-07 08:29:53Z

2

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

answered Dec 7, 2016 at 8:29

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dbz Over a year ago

Thanks @sagi , it works!!! but the problem is that I have a lot of types and its very difficult for me to make the query with all of them.

sagi Over a year ago

Then you should use Pivot , but to tell you the truth , if its not that many, (20 or so) I prefer to use this version .

dbz Over a year ago

they are like 800 types...its better to use pivot in my case?

Rémy Baron · Accepted Answer · 2016-12-07 09:15:22Z

0

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

answered Dec 7, 2016 at 9:15

Rémy Baron

1,4099 silver badges15 bronze badges

5 Comments

dbz Over a year ago

Hi @Rémy Baron I use your code combined with sagis code and the data output gives me a result with a lot of SELECT queries:

Rémy Baron Over a year ago

no just use my code , that generates one query based on sagi's query. Execute this generated query and you have your result

dbz Over a year ago

Im trying it but the console result is a text column with tuis result inside:

dbz Over a year ago

"SELECT t.id,t.date,MAX(CASE WHEN t.type= '486' THEN t.value  END) as 486_col,MAX(CASE WHEN t.type= '487' THEN t.value END) as 487_col,MAX(CASE WHEN t.type= '488' THEN t.value END) as 488_col,MAX(CASE WHEN t.type= '489'  (...)"

Rémy Baron Over a year ago

yes , it's your generated query now you can get this result and execute it as an new sql query

Collectives™ on Stack Overflow

PostgreSQL how to convert rows to colums

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related