CODEWITHSUNDEEP
sqlpostgresqltranspose
rjl
rjl

Reputation: 11

postgresql transpose rows an columns misery

I have gone through all of the similar topics and can't seem to solve my problem. I have a table like this:

id[PK]        key[PK]     value
--------------------------------
22            ingfs       east1
22            storage     stgeast1
23            ingfs       east2
23            storage     stgeast2
24            ingfs       east3
24            storage     stgeast3

I want to get:

ingfs   storage
----------------
east1   stgeast1
east2   stgeast2
east3   stgeast3

I have tried the pivot table example but don't seem to be able to get anything to work. Seems like this should be simple but I can't get my head wrapped around it. Thanks in advance for any assistance.

rjl

Upvotes: 1

Views: 1553

Answers (2)

roman
roman

Reputation: 117485

select
    max(case when key = 'ingfs' then value end) as ingfs,
    max(case when key = 'storage' then value end) as storage
from Table1
group by id
order by 1, 2

sql fiddle demo

Upvotes: 2

gsiems
gsiems

Reputation: 3790

Would the following work for you?

WITH i AS (
    SELECT id, 
            value 
        FROM table_name 
        WHERE key = 'ingfs'),
s AS (
    SELECT id, 
            value 
        FROM table_name 
        WHERE key = 'storage')
SELECT i.value AS ingfs, 
        s.value AS storage
    FROM i
    JOIN s on ( i.id = s.id ) ;

Upvotes: 0

Related Questions