PIVOT Oracle-将多行数据转换为具有多列的单行，没有聚合数据 [英] PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data
问题描述
我需要传输以下数据集:
I have a need to transfer the following data set:
，其中只有突出显示的行是感兴趣的行(("LN"，"SN")中的标记)，因为我仅对产品的序列号和批号感兴趣. 我想将上面的数据集转换为以下数据集:
in which only the highlighted lines are the one of interest (Tag in ('LN','SN')) as I am only interested in SerialNumber and LotNumber of the products. I would like to convert the data set above into the following data set:
它在第一行中列出产品及其序列号和批号.
Where it lists the product along with its serialnumber and lot number in 1 row.
在线阅读后，我认为可能是PIVOT所需要的.但是，我在声明的技术方面很挣扎.
After doing some reading online, I think PIVOT might be what I need. However, I am struggling with the technical side of the statement.
我尝试过:
select * from (
select * from TEST2 where tag in ('LN','SN')
)
PIVOT
(
max(value)
for tag in ('LN','SN')
)
order by category,subcat,item,"Date"
但这不会生成我想要的输出.有什么建议吗? PIVOT是正确使用的语句，还是在这种情况下更合适的其他语句?我意识到PIVOT需要聚合函数，但是我不计数也不添加任何东西.请告知.
but this does not generate the output I wanted. Any suggestion? Is PIVOT the correct statement to use or is there some other statement that is more appropriate in this case? I realize that PIVOT requires an aggregate function but I do not count or add anything. Please advise.
下面是我的测试表及其数据
Below is my test table with its data
CREATE TABLE "TEST2"
( "Date" DATE,
"SUBCAT" VARCHAR2(6 BYTE),
"CATEGORY" VARCHAR2(7 BYTE),
"VALUE" VARCHAR2(17 BYTE),
"ITEM" VARCHAR2(2 BYTE),
"DESCRIPTION" VARCHAR2(15 BYTE),
"TAG" VARCHAR2(3 BYTE)
)
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'25','OTHER ATTRIBUTE','OA');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'25','SOME COMMENTS','SC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','1105618','25','Lot Number','LN');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','12','25','NOT RELEVANT','NR');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','45','25','NOT USE','NU');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','-1','25','DO NOT CARE','DC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','3x12mm','25','Serial Number','SN');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'25','ABC','ABC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'25','Whatever','DEF');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'28','SOME ATTRIBUTE','SA');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'28','OTHER ATTRIBUTE','OA');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','isq: 75, 80','28','Other Comments','OC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'28','SOME COMMENTS','SC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','1303757','28','Lot Number','LN');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','12','28','NOT RELEVANT','NR');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','40','28','NOT USE','NU');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576','0','28','DO NOT CARE','DC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'28','ABC','ABC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('24-OCT-13','DD-MON-RR'),'290223','1219576',null,'28','Whatever','DEF');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576',null,'4','SOME ATTRIBUTE','SA');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576',null,'4','OTHER ATTRIBUTE','OA');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','1403114','4','Lot Number','LN');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','11','4','NOT RELEVANT','NR');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','50','4','NOT USE','NU');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','0','4','DO NOT CARE','DC');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576',null,'4','Whatever','DEF');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','7777777777','9','Lot Number','LN');
Insert into TEST2 ("Date",SUBCAT,CATEGORY,VALUE,ITEM,DESCRIPTION,TAG) values (to_date('18-JUN-15','DD-MON-RR'),'354506','1219576','9.999999999999E12','9','Serial Number','SN');
推荐答案
您对说明不做任何操作，说明也随标记而有所不同.它不是聚合的，因此它在隐式的"group by"中，因此在结果集中会得到单独的行.
You aren't doing anything with the description, which also varies with the tag. It isn't aggregated so it in the implicit 'group by', so you get separate rows in the result set.
您也可以使用另一个(虚拟)聚合来捕获它:
You can either capture that too with another (dummy) aggregate:
select * from (
select * from TEST2 where tag in ('LN', 'SN')
)
PIVOT
(
max(value) as value, max(description) as description
for tag in ('LN' as ln, 'SN' as sn)
)
order by category, subcat, item, "Date";
Date SUBCAT CATEGOR IT LN_VALUE LN_DESCRIPTION SN_VALUE SN_DESCRIPTION
--------- ------ ------- -- ----------------- --------------- ----------------- ---------------
24-OCT-13 290223 1219576 25 1105618 Lot Number 3x12mm Serial Number
24-OCT-13 290223 1219576 28 1303757 Lot Number
18-JUN-15 354506 1219576 4 1403114 Lot Number
18-JUN-15 354506 1219576 9 7777777777 Lot Number 9.999999999999E12 Serial Number
或更可能的是，如果不希望将其从中间结果集中排除，可以通过指定所需的列而不是使用*:
Or more likely exclude it from the intermediate result set if you don't want it, by specify the columns you do want instead of using *:
select * from (
select category, subcat, item, "Date", tag, value
from TEST2 where tag in ('LN', 'SN')
)
PIVOT
(
max(value) for tag in ('LN' as ln, 'SN' as sn)
)
order by category, subcat, item, "Date";
CATEGOR SUBCAT IT Date LN SN
------- ------ -- --------- ----------------- -----------------
1219576 290223 25 24-OCT-13 1105618 3x12mm
1219576 290223 28 24-OCT-13 1303757
1219576 354506 4 18-JUN-15 1403114
1219576 354506 9 18-JUN-15 7777777777 9.999999999999E12
这篇关于PIVOT Oracle-将多行数据转换为具有多列的单行，没有聚合数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
