CODEWITHSUNDEEP
sqlpostgresqlselectrow
Maruko
Maruko

Reputation: 1

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

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

Upvotes: 0

Views: 46

Answers (1)

Stu
Stu

Reputation: 32614

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

Upvotes: 1

Related Questions