unpivot with a flag in Oracle

Question

I have this table structure

ID,SUPPLIER_GROUP1,SUPPLIER1,SUPPLIER_GROUP2,SUPPLIER2.

i want to unpivot and get

ID,SUPPLIER_GROUP,SUPPLIER,TYPE

so each supplier_group and supplier values come to the appropriate column and in TYPE column will be either 1 or 2 to see if the SUPPLIER_GROUP and SUPPLIER value was supplier1 or supplier2 .

Answer 1

Use UNPIVOT with multiple column groups:

SELECT *
FROM   table_name
UNPIVOT (
  (supplier_group, supplier) FOR type IN (
    (supplier_group1, supplier1) AS 1,
    (supplier_group2, supplier2) AS 2
  )
);

Which, for the sample data:

CREATE TABLE table_name (ID,SUPPLIER_GROUP1,SUPPLIER1,SUPPLIER_GROUP2,SUPPLIER2) AS
SELECT 1, 'sg1.1', 's1.1', 'sg2.1', 's2.1' FROM DUAL UNION ALL
SELECT 2, 'sg1.2', 's1.2', 'sg2.2', 's2.2' FROM DUAL UNION ALL
SELECT 3, 'sg1.3', 's1.3', 'sg2.3', 's2.3' FROM DUAL

Outputs:

ID	TYPE	SUPPLIER_GROUP	SUPPLIER
1	1	sg1.1	s1.1
1	2	sg2.1	s2.1
2	1	sg1.2	s1.2
2	2	sg2.2	s2.2
3	1	sg1.3	s1.3
3	2	sg2.3	s2.3

db<>fiddle here