CODEWITHSUNDEEP
sqldb2pivotdb2-luw
AdamTheD
AdamTheD

Reputation: 19

DB2 Convert Each Column to a Row based on ID

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!

Upvotes: 0

Views: 484

Answers (1)

nfgl
nfgl

Reputation: 3202

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)

Upvotes: 1

Related Questions