DB2 Convert Each Column to a Row based on ID

Question

Not sure where to start with this, I've read about pivot/lateral but don't fully understand their use cases so I'm not sure if they apply here. They don't appear to be in my version of DB2 regardless.

Example Starting Table

ID, Firstname, Lastname, Age
0, John, Smith, 30
1, Jane, Doe, 40
2, Fake, Name, 50

I'm looking to pivot this table to the following...

Example Expected Results

Id, ColumnName, ColumnValue
0, Firstname, John
0, Lastname, Smith
0, Age, 30
1, Firstname, Jane
1, LastName, Doe
1, Age, 40
2, FirstName, Fake
2, LastName, Name
2, Age, 50

How should I go about doing this? Is there a name for this action?

Thanks!

Answer 1

Pivot syntax doesn't exist in DB2, you can use lateral like this:

with table1 (ID, Firstname, Lastname, Age) as (
  values
  (0, 'John', 'Smith', 30),
  (1, 'Jane', 'Doe', 40),
  (2, 'Fake', 'Name', 50)
)
select
  ID,rowtocolumns.*
from table1
  cross join lateral (
    values
    ('Firstname', Firstname),
    ('Lastname', Lastname),
    ('Age', varchar(age))
  ) as rowtocolumns(ColumnName, ColumnValue)