Stack multiple columns in one - SQL

Question

I have a table and the columns are 'Name' 'Product' 'Type' 'Date' 'Type1' 'Date1' 'Type2' 'Date2'..... (till type23 date23)

I want to stack Type1, Type2 under Type column and similarly Date1, Date2 under Date column. I don't want to use UNION ALL, so I am looking for other alternatives such as unpivot.

This is how my data looks like:

Name Product Type Date Type1 Date1 Type2 Date2
John AA1     xx   12/31 yy   10/30 zz    7/30

This is how I want it to look:

Name Product Type Date
John AA1     xx   12/31
John AA1     yy   10/30
John AA1     zz   7/30

How can I achieve this using cursor for loop or any other effective way? I have connected DB2 table, and I am using Oracle SQL Developer to query my data.

Hi - please format your data as tables so that it is readable — NickW
– NickW, Commented Apr 30, 2024 at 14:04
Oracle or DB2? They are different databases - not sure what you mean by "Oracle DB2". If it's Oracle, look at the UNPIVOT clause. — Alex Poole
– Alex Poole, Commented Apr 30, 2024 at 14:08
What is your Db2-server platform, Z/OS, i-series (as400), linux/unix/windows/cloud ? What is your Db2-server version? You should always write these facts into the Question when asking for Db2 help because the answer can depend on both. — mao
– mao, Commented Apr 30, 2024 at 15:00
I have connected DB2 TO Oracle SQL Developer and I am using Oracle SQL Developer to query my data — Twinkks
– Twinkks, Commented Apr 30, 2024 at 17:21

ASH · Accepted Answer · 2024-05-01 04:08:02Z

0

There are probably several ways to do this kind of thing. Here's one option.

CREATE TABLE your_table (
    Name VARCHAR(50),
    Product VARCHAR(50),
    Type VARCHAR(50),
    Date DATE,
    Type1 VARCHAR(50),
    Date1 DATE,
    Type2 VARCHAR(50),
    Date2 DATE
);

INSERT INTO your_table (Name, Product, Type, Date, Type1, Date1, Type2, Date2)
VALUES 
    ('John', 'AA1', 'xx', '2023-12-31', 'yy', '2023-10-30', 'zz', '2023-07-30')


select * from your_table


SELECT Name, Product, Type, Date FROM your_table WHERE Type1 IS NOT NULL
UNION ALL
SELECT Name, Product, Type1, Date1 FROM your_table WHERE Type1 IS NOT NULL
UNION ALL
SELECT Name, Product, Type2, Date2 FROM your_table WHERE Type2 IS NOT NULL;

Result:

answered May 1, 2024 at 4:08

ASH

20.5k28 gold badges116 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dtc348 · Accepted Answer · 2024-05-02 08:03:45Z

0

With the help of Lateral in DB2, you can achieve your result.


WITH t1 (
  Name, Product, TYPE, Date, Type1, Date1, 
  Type2, Date2
) AS (
  VALUES 
    (
      'John', 'AA1', 'xx', '12/31', 'yy', 
      '10/30', 'zz', '7/30'
    )
) 
SELECT 
  Name, 
  Product, 
  TransposedType as Type, 
  TransposedDate AS Date 
FROM 
  t1 CROSS 
  JOIN LATERAL (
    VALUES 
      (Type, Date), 
      (Type1, Date1), 
      (Type2, Date2)
  ) AS Transposed(TransposedType, TransposedDate);

db<>fiddle

Output:

#|NAME|PRODUCT|TYPE|DATE |
-+----+-------+----+-----+
1|John|AA1    |xx  |12/31|
2|John|AA1    |yy  |10/30|
3|John|AA1    |zz  |7/30 |

answered May 2, 2024 at 8:03

dtc348

3647 silver badges21 bronze badges

Collectives™ on Stack Overflow

Stack multiple columns in one - SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related