I have a table with the data similar to the original data. By using the original data, i want to restructure to the modified data like in the table using PIVOT or UNPIVOT in SQL.
Original Data
| lot | table1 | table2 | table3 | table4 | ... | table28 | headno | … |
|---|---|---|---|---|---|---|---|---|
| a | xxa | xxb | xxc | xxd | ... | xxf | 1 | … |
| b | xxg | xxe | xxi | xxp | ... | xxx | 1 | … |
| c | xxk | xxm | xxr | xxt | ... | xxu | 4 | … |
| … | ... | ... | ... | ... | ... | ... | … | … |
result after modification
| lot | table | table NO | head no | … |
|---|---|---|---|---|
| a | xxa | table1 | 1 | … |
| a | xxb | table2 | 1 | … |
| a | xxc | table3 | 1 | … |
| a | xxd | table4 | 1 | … |
| a | ... | …. | … | … |
| b | xxg | table1 | 1 | … |
| b | xxe | table2 | 1 | … |
| b | xxi | table3 | 1 | … |
| b | xxp | table4 | 1 | … |
| b | ... | … | … | … |
| c | xxk | table1 | 4 | … |
| … | … | … | … | … |
Thanks in advance.
My technique to unpivot is to abide by a general SQL standard (one that is not limited to running on a specific database).
Un-pivoting, in that case, is about CROSS JOIN-ing the 'horizontal' table with an in-line table i consisting of the integer column i and of as many consecutive integers as there are horizontal columns to pivot - and a CASE WHEN expression to use the right column depending on the current value of i in the current result set:
WITH
-- your input - 5 columns work the same as 28 ...
-- do not use in your real query.
indata(lot,table1,table2,table3,table4,table5,headno) AS (
SELECT 'a','xxa','xxb','xxc','xxd','xxf',1 FROM dual
UNION ALL SELECT 'b','xxg','xxe','xxi','xxp','xxx',1 FROM dual
UNION ALL SELECT 'c','xxk','xxm','xxr','xxt','xxu',4 FROM dual
)
-- real query starts here, replace following comma with "WITH"
,
-- need series of integers - 28 rather than 5 in the full example ..
i(i) AS (
SELECT 1 FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
)
SELECT
lot
, CASE i
WHEN 1 THEN table1
WHEN 2 THEN table2
WHEN 3 THEN table3
WHEN 4 THEN table4
WHEN 5 THEN table5
ELSE NULL
END AS tbl
, 'table'||CAST(i AS VARCHAR(1)) AS table_NO
, headno
FROM indata CROSS JOIN i
ORDER BY lot,i
-- out lot | tbl | table_NO | headno
-- out -----+-----+----------+--------
-- out a | xxa | table1 | 1
-- out a | xxb | table2 | 1
-- out a | xxc | table3 | 1
-- out a | xxd | table4 | 1
-- out a | xxf | table5 | 1
-- out b | xxg | table1 | 1
-- out b | xxe | table2 | 1
-- out b | xxi | table3 | 1
-- out b | xxp | table4 | 1
-- out b | xxx | table5 | 1
-- out c | xxk | table1 | 4
-- out c | xxm | table2 | 4
-- out c | xxr | table3 | 4
-- out c | xxt | table4 | 4
-- out c | xxu | table5 | 4