How to expand columns into individual timesteps in PostgreSQL-CodePudding

I have a table of columns that represent a time series. The datatypes are not important, but anything after timestep2 could potentially be NULL.

| id | timestep1 | timestep2 | timestep3 | timestep4 |
|----|-----------|-----------|-----------|-----------|
| a  | foo1      | bar1      | baz1      | qux1      |
| b  | foo2      | bar2      | baz2      | NULL      |

I am attempting to retrieve a view of the data more suitable for modeling. My modeling use-case requires that I break each time series (row) into rows representing their individual "states" at each step. That is:

| id | timestep1 | timestep2 | timestep3 | timestep4 |
|----|-----------|-----------|-----------|-----------|
| a  | foo1      | NULL      | NULL      | NULL      |
| a  | foo1      | bar1      | NULL      | NULL      |
| a  | foo1      | bar1      | baz1      | NULL      |
| a  | foo1      | bar1      | baz1      | qux1      |
| b  | foo2      | NULL      | NULL      | NULL      |
| b  | foo2      | bar2      | NULL      | NULL      |
| b  | foo2      | bar2      | baz2      | NULL      |

How can I accomplish this in PostgreSQL?

CodePudding user response：

Use UNION.

select id, timestep1, timestep2, timestep3, timestep4
from my_table

union

select id, timestep1, timestep2, timestep3, null
from my_table

union

select id, timestep1, timestep2, null, null
from my_table

union

select id, timestep1, null, null, null
from my_table

order by 
    id, 
    timestep2 nulls first, 
    timestep3 nulls first, 
    timestep4 nulls first

There is a more compact solution, maybe more convenient when dealing with a greater number of timesteps:

select distinct
    id, 
    timestep1, 
    case when i > 1 then timestep2 end as timestep2, 
    case when i > 2 then timestep3 end as timestep3, 
    case when i > 3 then timestep4 end as timestep4 
from my_table
cross join generate_series(1, 4) as i
order by 
    id, 
    timestep2 nulls first, 
    timestep3 nulls first, 
    timestep4 nulls first

Test it in Db<>fiddle.