旋转具有默认值的多个表 [英] Pivot Multiple Tables with Default Values
本文介绍了旋转具有默认值的多个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
说我有下表(示例DDL和Post末尾的插入语句):
Say I have the following tables (sample DDLs and Insert Statements at end of Post):
XX_SAMPLE_ELEMENTS
ELEMENT_NAME ELEMENT_ENTRY_ID
--------------------------- ---------------
Regular Wages 72249260
Workers Compensation 72249256
VERTEX 72249257
ALC Workers Compensation 72249258
US_TAX_VERTEX 72249259
PTO Taken Plan 72523856
XX_ELEMENT_VALUES
INPUT_VALUE_ID NAME
-------------- ------------------------
30921 Pay Value
455 Pay Value
391 Pay Value
392 Jurisdiction
235 Rate Code
30921 Pay Value
236 Jurisdiction
30922 Continuous Service Date
234 Rate
1425 Pay Value
16180 Pay Value
456 Jurisdiction
394 Calc_Mode
395 Net
233 Pay Value
393 Percentage
XX_ENTRY_VALUES
ELEMENT_ENTRY_VALUE_ID INPUT_VALUE_ID ELEMENT_ENTRY_ID SCREEN_ENTRY_VALUE
----------------------- -------------- ----------------- -------------------
532333350 30921 72249253
532333351 30922 72249253
532333352 1425 72249254
532333353 16180 72249255
532333354 455 72249256
532333355 456 72249256 33-000-0000
532333356 391 72249257
532333357 392 72249257 33-000-0000
532333358 393 72249257 0
532333359 394 72249257
532333360 395 72249257
532333361 391 72249258
532333362 392 72249258 33-065-0000
532333363 393 72249258 0
532333364 394 72249258
532333365 395 72249258
532333366 391 72249259
532333367 392 72249259 33-065-2920
532333368 393 72249259 100
532333369 394 72249259
532333370 395 72249259
532333371 233 72249260
532333372 234 72249260 9.7
532333373 235 72249260
532333374 236 72249260
532697988 391 72523856
532697989 392 72523856 33-065-3190
532697990 393 72523856 0
532697991 394 72523856
532697992 395 72523856
我需要数据如下所示:
element_name element_entry_id input_value_id1 input_value_id2 input_value_id3 input_value_id4 input_value_id5 input_value_id6 screen_entry_value1 screen_entry_value2 screen_entry_value3 screen_entry_value4 screen_entry_value5 screen_entry_value6 name1 name2 name3 name4 name5 name6
------------------------- ---------------- --------------- --------------- --------------- --------------- --------------- --------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------- -------------- --------------- -------------- -------------- --------------
ALC Workers Compensation 72249258 393 395 394 391 392 392 0 NULL NULL NULL 33-065-0000 33-065-3190 Percentage Net Calc_Mode Pay Value Jurisdiction Jurisdiction
PTO Taken Plan 72523856 392 391 393 395 394 NULL 33-065-3190 NULL 0 NULL NULL NULL Jurisdiction Pay Value Percentage Net Calc_Mode NULL
Regular Wages 72249260 234 236 235 233 NULL NULL 9.7 NULL NULL NULL NULL NULL Rate Jurisdiction Rate Code Pay Value NULL NULL
US_TAX_VERTEX 72249259 391 394 393 395 392 NULL NULL NULL 100 NULL 33-065-2920 NULL Pay Value Calc_Mode Percentage Net Jurisdiction NULL
VERTEX 72249257 393 395 391 392 394 NULL 0 NULL NULL 33-000-0000 NULL NULL Percentage Net Pay Value Jurisdiction Calc_Mode NULL
Workers Compensation 72249256 455 456 NULL NULL NULL NULL NULL 33-000-0000 NULL NULL NULL NULL Pay Value Jurisdiction NULL NULL NULL NULL
到目前为止，我有以下查询:
So far, i have the below query:
SELECT lmnt.element_name
, lmnt.element_entry_id
, nval.input_value_id
, nval.screen_entry_value
, eval.name
FROM XX_SAMPLE_ELEMENTS lmnt
, XX_ENTRY_VALUES nval
, XX_ELEMENT_VALUES eval
where lmnt.element_entry_id = nval.element_entry_id
and eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID
order by lmnt.element_name;
问题
当元素没有固定数量的值时，如何旋转列input_value_id，screen_entry_value和name?
(即，PTO采取的计划有5个要素(管辖权，工资值，百分比，净额，Calc_Mode)，而工人补偿只有2个要素(工资值和管辖权).
How do I Pivot the columns input_value_id, screen_entry_value and name, when the Elements do not have a fixed number of values?
(i.e. PTO Taken Plan has 5 Elements (Jurisdiction, Pay Value, Percentage, Net, Calc_Mode) while Workers Compensation only has 2 (Pay Value and Jurisdiction)).
注释
- 我无法修改表结构，因为这些表是原始产品.
- SQL是首选方法，但是如果无法用SQL解决方案，则PL/SQL会很好.
- 数据库版本:Oracle Database 11g企业版11.2.0.4.0版-64位生产
DDL和插入语句
CREATE TABLE XX_SAMPLE_ELEMENTS
(
ELEMENT_NAME VARCHAR2(100)
, ELEMENT_ENTRY_ID NUMBER
);
CREATE TABLE XX_ELEMENT_VALUES
(
INPUT_VALUE_ID NUMBER
, NAME VARCHAR2(100)
);
CREATE TABLE XX_ENTRY_VALUES
(
ELEMENT_ENTRY_VALUE_ID NUMBER
, INPUT_VALUE_ID NUMBER
, ELEMENT_ENTRY_ID NUMBER
, SCREEN_ENTRY_VALUE VARCHAR2(100)
);
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('Regular Wages', '72249260');
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('Workers Compensation','72249256');
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('VERTEX','72249257');
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('ALC Workers Compensation','72249258');
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('US_TAX_VERTEX','72249259');
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('PTO Taken Plan','72523856');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (455,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (391,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (392,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (235,'Rate Code');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (30921,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (236,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (30922,'Continuous Service Date');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (234,'Rate');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (1425,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (16180,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (456,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (394,'Calc_Mode');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (395,'Net');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (233,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (393,'Percentage');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333350', '30921', '72249253', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333351', '30922', '72249253', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333352', '1425', '72249254', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333353', '16180', '72249255', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333354', '455', '72249256', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333355', '456', '72249256', '33-000-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333356', '391', '72249257', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333357', '392', '72249257', '33-000-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333358', '393', '72249257', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333359', '394', '72249257', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333360', '395', '72249257', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333361', '391', '72249258', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333362', '392', '72249258', '33-065-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333363', '393', '72249258', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333364', '394', '72249258', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333365', '395', '72249258', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333366', '391', '72249259', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333367', '392', '72249259', '33-065-2920');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333368', '393', '72249259', '100');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333369', '394', '72249259', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333370', '395', '72249259', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333371', '233', '72249260', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333372', '234', '72249260', '9.7');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333373', '235', '72249260', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333374', '236', '72249260', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697988', '391', '72523856', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697989', '392', '72523856', '33-065-3190');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697990', '393', '72523856', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697991', '394', '72523856', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697992', '395', '72523856', NULL);
推荐答案
此查询可以作为更好的实验基础的良好开端:
This query can be a good start as a base for furhter experimens:
With src As (
SELECT lmnt.element_name
, lmnt.element_entry_id
, nval.input_value_id
, nval.screen_entry_value
-- , eval.name
, row_number() over (partition by lmnt.element_name, lmnt.element_entry_id
order by lmnt.element_name ) rn
FROM XX_SAMPLE_ELEMENTS lmnt
, XX_ENTRY_VALUES nval
, XX_ELEMENT_VALUES eval
where lmnt.element_entry_id = nval.element_entry_id
and eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID
order by lmnt.element_name
)
SELECT * FROM src
PIVOT (
max( input_value_id ) As input_value,
min( screen_entry_value ) as screen_entry_value
FOR (rn)
IN (1,2,3,4,5)
)
这篇关于旋转具有默认值的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
