PIVOT XML - dynamic extracting field out to columns

Question

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:

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');

Answer 1

... 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.)