sql - PIVOT XML - dynamic extracting field out to columns -
i have posted original question on thread:
pivot oracle - transform multiple row data single row multiple columns, no aggregate data enter link description here
the results wanted achieved using statement:
select * ( select "date", subcat,category,item,tag,value test2 tag in ('ln','sn') ) pivot ( max(value) tag in ('ln','sn') ) order category,subcat,item,"date" however, expand solution more flexible , accept dynamic selection in in clause. oracle not , threw out error. instructed use pivot xml. after research, came statement:
select * ( select "date", subcat,category,item,tag,value test2 tag in ( select 'ln' dual union select 'sn' dual ) ) pivot xml ( max(value) tag in (any) ) order category,subcat,item,"date" but have extract out fields wanted not familiar how proceed. please help.
the final result want still same:
with pivot xml, got whole bunch of ugly xml tags!
the data associated question is:
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 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 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 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 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 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 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 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 test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','abc','abc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'25','whatever','def'); insert 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 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 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 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 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 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 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 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 test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','abc','abc'); insert test2 ("date",subcat,category,value,item,description,tag) values (to_date('24-oct-13','dd-mon-rr'),'290223','1219576',null,'28','whatever','def'); insert 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 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 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 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 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 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 test2 ("date",subcat,category,value,item,description,tag) values (to_date('18-jun-15','dd-mon-rr'),'354506','1219576',null,'4','whatever','def'); insert 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 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');
... accept dynamic selection in in clause ...
not possible without xml-pivoting.
depending on need result set next, options are
- use xslt transform pivoted xml whatever need.
- instead of xml-pivot, construct query's pivot
inclause @ runtime (and make cursor, insert/select, ctas, ... whatever need.)
Comments
Post a Comment