CODEWITHSUNDEEP
sqlfor-loopdb2database-cursor
Twinkks
Twinkks

Reputation: 1

Stack multiple columns in one - SQL

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.

Upvotes: 0

Views: 132

Answers (2)

dtc348
dtc348

Reputation: 364

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 |

Upvotes: 0

ASH
ASH

Reputation: 20342

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:

enter image description here

Upvotes: 0

Related Questions