Postgresql query to transpose columns to rows

Question

I have a table like this one:

id_edifc classe_a classe_b classe_c
9001 0 0 1
9002 1 1 1
9003 0 1 2
9004 0 0 0
9005 1 1 0

and I would like to visualize it like this instead:

id_edifc classe
9001 classe_c
9002 classe_a
9002 classe_b
9002 classe_c
9003 classe_b
9003 classe_c
9003 classe_c
9004 NULL
9005 classe_a
9005 classe_b

Lukasz Szozda · Accepted Answer · 2020-12-01 20:26:08Z

2

Using LATERAL:

SELECT t.id_edifc, s.name
FROM tab t
,LATERAL ( VALUES (classe_a, 'classe_a')
                 ,(classe_b, 'classe_b')
                 ,(classe_c, 'classe_c'))s(val, name)
WHERE val = 1;

To handle all zeros in row and replicating values:

SELECT t.id_edifc, s2.name
FROM tab t
LEFT JOIN LATERAL (SELECT s.name
                  FROM (VALUES  (classe_a, 'classe_a')
                               ,(classe_b, 'classe_b')
                               ,(classe_c, 'classe_c'))s(val, name)
                   ,LATERAL generate_series(1,s.val)
                   WHERE val > 0
                 ) s2 ON TRUE
ORDER BY t.id_edifc;

db<>fiddle demo

Output:

+-----------+----------+
| id_edifc  |   name   |
+-----------+----------+
|     9001  | classe_c |
|     9002  | classe_a |
|     9002  | classe_b |
|     9002  | classe_c |
|     9003  | classe_b |
|     9003  | classe_c |
|     9003  | classe_c |
|     9004  | NULL     |
|     9005  | classe_a |
|     9005  | classe_b |
+-----------+----------+

edited Dec 1, 2020 at 20:26

answered Dec 1, 2020 at 20:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

ishanka ganepola Over a year ago

thanks lukasz but I want only those with corresponding value greater than zero to appear... and to appear as many times as the value is worth

Lukasz Szozda Over a year ago

@ishankaganepola

I want only those with corresponding value greater than zero to appear... and to appear as many times as the value is worth

- 9003 clase_c is twice, it was replicated as many times as needed

Mike Organek · Accepted Answer · 2020-12-01 21:00:45Z

Using jsonb functions allows you to do this for an arbitrary number of columns:

with pvt as (
  select id_edifc, k as classe
    from xpose
         cross join lateral to_jsonb(xpose)  as j(obj)
         cross join lateral jsonb_each_text(obj - 'id_edifc') as e(k, v)
         cross join lateral generate_series(1, v::int)
)
select x.id_edifc, p.classe
  from xpose x
       left join pvt p
         on p.id_edifc = x.id_edifc
 order by x.id_edifc, p.classe;

┌──────────┬──────────┐
│ id_edifc │  classe  │
├──────────┼──────────┤
│     9001 │ classe_c │
│     9002 │ classe_a │
│     9002 │ classe_b │
│     9002 │ classe_c │
│     9003 │ classe_b │
│     9003 │ classe_c │
│     9003 │ classe_c │
│     9004 │          │
│     9005 │ classe_a │
│     9005 │ classe_b │
└──────────┴──────────┘
(10 rows)

Collectives™ on Stack Overflow

Postgresql query to transpose columns to rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related