How can I extract multiple rows from a single one having 4 columns in PostgreSQL?

Question

I'm having a issue with a query in Postgres. I need to extract from a table with a specific pattern using a pure select. For example I have:

Column A	Column B	Column C	Column D
1	2	3	4

From this table I want to select something like this:

Column A	Column B	Column C	Column D
1	null	null	null
1	2	null	null
1	2	3	null
1	2	3	4

I don't really have a clue on how to do it efficiently. Anyone can help? Many thanks

is 1, 2, 3, 4 the only combination, or can there be something else. Also, is column A always less than Column B etc — siggemannen
– siggemannen, Commented Jan 19, 2023 at 16:12

Stu · Accepted Answer · 2023-01-19 14:32:13Z

1

You can cross join with a list of integers and use a conditional expression:

with n as (select * from(values(1),(2),(3),(4))x(n))
select 
    case when n >= cola then cola end cola,
    case when n >= colb then colb end colb,
    case when n >= colc then colc end colc,
    case when n >= cold then cold end cold
from n
cross join t;

working Fiddle

answered Jan 19, 2023 at 14:32

Stu

32.7k6 gold badges17 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

How can I extract multiple rows from a single one having 4 columns in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related