CODEWITHSUNDEEP
postgresqlunpivot
Santhosh Sadasivapet
Santhosh Sadasivapet

Reputation: 11

Unpivot data in PostgreSQL

I have a table in PostgreSQL with the below values,

empid    hyderabad    bangalore    mumbai    chennai
 1        20           30           40        50
 2        10           20           30        40

And my output should be like below

empid    city         nos
1        hyderabad    20
1        bangalore    30
1        mumbai       40
1        chennai      50
2        hyderabad    10
2        bangalore    20
2        mumbai       30
2        chennai      40

How can I do this unpivot in PostgreSQL?

Upvotes: 1

Views: 239

Answers (2)

marcothesane
marcothesane

Reputation: 6721

Or this one: simpler to read- and real plain SQL ...

WITH
input(empid,hyderabad,bangalore,mumbai,chennai) AS (
          SELECT 1,20,30,40,50
UNION ALL SELECT 2,10,20,30,40
)
,
i(i) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
)
SELECT
  empid
, CASE i 
    WHEN 1 THEN 'hyderabad'
    WHEN 2 THEN 'bangalore'
    WHEN 3 THEN 'mumbai'
    WHEN 4 THEN 'chennai'
    ELSE        'unknown'
  END AS city
, CASE i 
    WHEN 1 THEN hyderabad
    WHEN 2 THEN bangalore
    WHEN 3 THEN mumbai
    WHEN 4 THEN chennai
    ELSE        NULL::INT
  END AS city 
FROM input CROSS JOIN i
ORDER BY empid,i;
-- out  empid |   city    | city 
-- out -------+-----------+------
-- out      1 | hyderabad |   20
-- out      1 | bangalore |   30
-- out      1 | mumbai    |   40
-- out      1 | chennai   |   50
-- out      2 | hyderabad |   10
-- out      2 | bangalore |   20
-- out      2 | mumbai    |   30
-- out      2 | chennai   |   40

Upvotes: 0

user330315
user330315

Reputation:

You can use a lateral join:

select t.empid, x.city, x.nos
from the_table t
  cross join lateral (
     values 
       ('hyderabad', t.hyderabad),
       ('bangalore', t.bangalore),
       ('mumbai', t.mumbai),
       ('chennai', t.chennai)
  ) as x(city, nos)
order by t.empid, x.city;

Upvotes: 3

Related Questions