CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivot-xml
user1205746
user1205746

Reputation: 3366

PIVOT XML - dynamic extracting field out to columns

I have posted an original question on this thread:

PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data enter link description here

The results I wanted was achieved using this statement:

select * from (
select "Date", subcat,category,item,tag,value from TEST2 where tag in ('LN','SN')
)
PIVOT 
(
  max(value)
  for tag in ('LN','SN')
)
order by category,subcat,item,"Date"

However, I would like to expand the solution to be more flexible and accept a dynamic selection in the IN clause. Oracle does not like it and threw out an error. I was instructed to use pivot xml. After some research, I came up with this statement:

select * from (
select "Date", subcat,category,item,tag,value from TEST2 
where tag in 
   (
    Select 'LN' from dual 
     union 
    select 'SN' from dual
    )
)
PIVOT xml
(
  max(value)
  for tag in (any)
)
order by category,subcat,item,"Date"

But I will have to extract out the fields I wanted which I am not very familiar with how to proceed. Please help.

The final result I want is still the same:

enter image description here

With PIVOT XML, I got a whole bunch of ugly xml tags!

The data associated with my 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 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');

Upvotes: 3

Views: 4923

Answers (2)

brahmananda Kar
brahmananda Kar

Reputation: 59

Here is one hack that works 


with a as (select to_char(xmltype.getclobval(JOB_XML))  k  from (
select * from (select ename,job from emp)

pivot xml ( max(ename) for job in (select job from emp))))

SELECT EXTRACTVALUE(VALUE(xml_list), '//column[1]') AS interface_no
      ,EXTRACTVALUE(VALUE(xml_list), '//column[2]') AS interface_name_a
  FROM 
TABLE(XMLSEQUENCE(EXTRACT(XMLType('<?xml version="1.0" encoding="UTF-8"?>'||(select * from a) ), 'PivotSet/item'))) xml_list;

Upvotes: 1

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

... accept a dynamic selection in the IN clause ...

Not possible without the XML-pivoting.

Depending on what you need to do with the result set next, your options are

  1. Use XSLT to transform that pivoted XML to whatever you need.
  2. Instead of XML-pivot, construct your query's pivot IN clause at runtime (and make it a cursor, an insert/select, a CTAS, ... whatever you need.)

Upvotes: 2

Related Questions