在SQL中使用PIVOT和UNPIVOT进行数据重组

Question

我有一个表，其中的数据与原始数据相似。通过使用原始数据，我希望像在SQL中使用PIVOT或UNPIVOT的表中那样，将数据重组为修改后的数据。

原始数据

表1表3>表4tbody>xxc>td>xxd>>td>xxt<td…>

	批次	表2
a	xxa	xxb	xxf	1	…
b	xxg	xxe	xxi	xxxp	xxx	1	…
	xxk	xxm	xxr		xxu	4	…
…							…

我的取消ivot技术是遵守通用SQL标准(不限于在特定数据库上运行(。

在这种情况下，取消数据透视是指将"水平"表与一个内联表i交叉联接，该表由整数列i和需要数据透视的水平列中的连续整数组成，并使用case WHEN表达式根据当前结果集中i的当前值使用右列：

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

您需要UNPIVOT:

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

对于样本数据：

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;

输出：

<1><1><1><1><1><1><1><1><1>44

LOT	表格	表格
a	xxa	表1
a	xxb	表2	1
a	xxc	表3
a	xxd	表4
a	xxf	表格28
b	xxg	表1
b	xxe	表2
b	xxi	表3
b	xxp	表4
b	xxx	表格28
c	xxk	表1		4
c	xxm	表2
c	xxr	表3		4
c	xxt	表4
c	xxu	表格28		4