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

result after modification

Thanks in advance.

Original data and Modified data

Original data and Modified data Updated version

CodePudding user response：

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 ...
indata(lot,table1,table2,table3,table4,table5,headno) AS (
          SELECT 'a','xxa','xxb','xxc','xxd','xxf',1
UNION ALL SELECT 'b','xxg','xxe','xxi','xxp','xxx',1
UNION ALL SELECT 'c','xxk','xxm','xxr','xxt','xxu',4
)
,
-- 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

CodePudding user response：

You need UNPIVOT:

SELECT lot,
       "TABLE",
       tableno,
       headno
       /*,...*/
FROM   table_name
UNPIVOT (
  "TABLE" FOR tableno IN (
    table1, table2, table3, table4, /*...,*/ table28
  )
)

Which, for the sample data:

CREATE TABLE table_name (lot, table1, table2, table3, table4, /*...,*/ table28, 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;

Outputs:

LOT	TABLE	TABLENO	HEADNO
a	xxa	TABLE1	1
a	xxb	TABLE2	1
a	xxc	TABLE3	1
a	xxd	TABLE4	1
a	xxf	TABLE28	1
b	xxg	TABLE1	1
b	xxe	TABLE2	1
b	xxi	TABLE3	1
b	xxp	TABLE4	1
b	xxx	TABLE28	1
c	xxk	TABLE1	4
c	xxm	TABLE2	4
c	xxr	TABLE3	4
c	xxt	TABLE4	4
c	xxu	TABLE28	4

db<>fiddle here