CODEWITHSUNDEEP
sqlpostgresql
Garfield
Garfield

Reputation: 143

Postgres: Crosstab function alternatives

I have a table like this:

ID | Value
------------
1  | ABC
1  | DEF
1  | GHI
2  | JKL
2  | MNO

I am looking for an output like this:

ID | Column1 | Column2 | Column3
----------------------------------
1  |   ABC   |   DEF   |   GHI     
2  |   JKL   |   MNO   |   NULL

Is there a way to achieve this in Postgres without using the crosstab function?

Upvotes: 1

Views: 450

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If the values are limited, then you can do conditional aggregation : 

select id, 
       max(value) filter (where seq = 1) as col1,
       max(value) filter (where seq = 2) as col2,
       max(value) filter (where seq = 3) as co3
from(select t.*, row_number() over (partition by id order by value) as seq
     from table t
    ) t
group by id;

Upvotes: 3

zealous
zealous

Reputation: 7503

You can use case statement. here is the demo.

select
    id,
    max(case when value in ('ABC', 'JKL') then value end) as column1,
    max(case when value in ('DEF', 'MNO') then value end) as column2,
    max(case when value in ('GHI') then value end) as column3
from myTable
group by
    id
order by
    id

Output:

id | column1 | column2 | column3
----------------------------------
1  |   ABC   |   DEF   |   GHI     
2  |   JKL   |   MNO   |   null

Upvotes: 0

Related Questions