Get multiple values for one row on postgresql

Question

I have a table with multiple dates and one ID and many strings. Each ID have multiple dates and strings, but only one ID.

I want to get all the dates value for one ID (for example ID=INI111) and get each date as a single value. I mean, I don't want all the dates on a single row, I want one row for each date. I want this too with the strings, but the important is the dates.

When I said "row" I mean to the results of the query.

For example:

Data:

ID ----------- Date1 ---------- String1 ---------- Date2 ----------- String2 ----- Date3 ------- String 3 INI100------- 2020/02/02 ---- aaaaa -------- 2020/02/03 ------- bbbbb ---- 2020/02/04--cccccc

Output:

1. 2020/02/02
2. aaaaa
3. 2020/02/03
4. bbbbb
5. 2020/02/04
6. ccccc

Anyone can helps me? Thanks a lot!

Answer 1

You seem to want to unpivot your dataset. In Postgres, you can do this with a lateral join:

select v.*
from mytable t
cross join lateral(
    values(id, date1::text), (id, string1), (id, date2::text), (id, string2)
) v(id, val)

Note that this requires adjusting the datatypes in the generated column (the query casts dates to strings).

Demo on DB Fiddle:

id     | val       
:----- | :---------
INI100 | 2020-02-02
INI100 | aaaaa     
INI100 | 2020-02-03
INI100 | bbbbb