Question 303417 in PostgreSQL Question posted in
The official documentation can be found here.

How to get columns as row values in postgresql

I have a table called fund_flag and it has columns like fund_type, fund1, fund2, fund3, fund4, fund5, fund6, sign_val

and table data is as follows.

fund_type fund1 fund2 fund3 fund4 fund5 fund6 sign_val
SGP Y N N N Y N –
USD Y N Y Y Y N +
INR N N Y N Y Y –

I want output like :

fund_type fund_name fund_value sign_val
SGP fund1 Y –
SGP fund2 N –
SGP fund3 N –
SGP fund4 N –
SGP fund5 Y –
SGP fund6 N –
USD fund1 Y +
USD fund2 N +
USD fund3 Y +
USD fund4 Y +
USD fund5 Y +
USD fund6 N +
in the same way for INR

Please help me with the query.

Tags:

2

Answers


  1. 0 
    SELECT fund_type, unnest(array['fund1', 'fund2', 'fund3', 'fund4', 'fund5', 'fund6']) AS fund_name, 
                  unnest(array[fund1, fund2, fund3, fund4, fund5, fund6]) AS fund_value, sign_val
FROM fund_flag;
    Login or Signup to reply.
  2. 0

    In Oracle or SQL Server this could be achieved with the UNPIVOT operator, but that is not available in Postgres. However You could use VALUES() and JOIN LATERAL to unpivot the columns :

    select f.fund_type, t.*, f.sign_val
from fund_flag f
  cross join lateral (
     values 
       (f.fund1, 'fund1'),
       (f.fund2, 'fund2'),
       (f.fund3, 'fund3'),
       (f.fund4, 'fund4'),
       (f.fund5, 'fund5'),
       (f.fund6, 'fund6')
  ) as t(fund_name, fund_value)
order by fund_type;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search