Postgres table selecting multiple columns and dynamically converting the result (column) into row - transposing column to rows

Question

I have a table (Damage) structure of the following type

  Rowid    damageTypeACount      damageTypeBCount damageTypeCCount
  1        23                     44              33

And also I have a requirement to read these table rows with result as ( title, id are set manually), sort of making transposing to columns to rows but with additional properties

  id           damagecountsum          label
    1           23                  Damage Type A
    2           44                  Damage Type B
    3           33                  Damage Type C

I did the following query and it works but wondering if there is a better way

     SELECT 1 as id,
             damageTypeACount as damagecount,
            "Damage Type A Count" as label
     FROM Damage where rowId=1
      UNION ALL
     SELECT 2 as id,
            damageTypeBCount as damagecount,
            "Damage Type B Count" as label
     FROM Damage where rowId=1
     UNION ALL
     SELECT 3 as id,
           damageTypeCCount as damagecount,
            "Damage Type C Count" as label
     FROM Damage where rowId=1

The above query works as expected but I was wondering if it is possible to do this in a single select statement transposing the columns into rows

GMB · Accepted Answer · 2020-07-10 13:42:54Z

2

You can unpivot with a lateral join:

select x.*
from damage d
cross join lateral (values
    (d.rowId, d.damageTypeACount, 'Damage Type A'),
    (d.rowId, d.damageTypeBCount, 'Damage Type B'),
    (d.rowId, d.damageTypeCCount, 'Damage Type C')
) as x(id, damagecount, label)

This reaffects the original id to each generated row. You can also generate new ids with row_number():

select row_number() over(order by id, label) id, x.*
from damage d
cross join lateral (values
    (d.rowId, d.damageTypeACount, 'Damage Type A'),
    (d.rowId, d.damageTypeBCount, 'Damage Type B'),
    (d.rowId, d.damageTypeCCount, 'Damage Type C')
) as x(rowId, damagecount, label)

You can filter the resultset with a where clause if needed:

where d.rowId = 1

answered Jul 10, 2020 at 13:42

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Dan Hunex Over a year ago

thank you. that works. I modified the requirment a little bit , will the same query work?

Collectives™ on Stack Overflow

Postgres table selecting multiple columns and dynamically converting the result (column) into row - transposing column to rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related