Search code examples
sqlpostgresqlrollup

How to get column values repeated in new rows in POSTGRESQL

I have a table with columns like below

A B C D E F G H

where A B are names and C D E F G H are measures

I would like to get the following using rollup function in postgresql.

A B C
A B D
A B E
A B F
A B G
A B H

The table itself is complicated I must get the first seven columns to appear in each row then the rest 6 columns should only get their values one at a time repeated 6 times + in each row the seven column values should appear with them using rollup function like example given above.

Your help is appreciated.

Solution

  • You can use grouping sets:

    select a, b, coalesce(c, d, e, f, g, h)
from t
group by grouping sets ( (a, b, c),
                         (a, b, d),
                         (a, b, e),
                         (a, b, f),
                         (a, b, g),
                         (a, b, h)
                       );