title: PIVOT Oracle - transform multiple row data to single row with multiple columns, no aggregate data
body: <p>I have a need to transfer the following data set:</p>
  
  <p><img src="https://i.stack.imgur.com/pVsxb.png" alt="enter image description here"></p>
  
  <p>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:
  <img src="https://i.stack.imgur.com/kcjlC.png" alt="enter image description here"></p>
  
  <p>Where it lists the product along with its serialnumber and lot number in 1 row.</p>
  
  <p>After doing some reading online, I think PIVOT might be what I need. However, I am struggling with the technical side of the statement.</p>
  
  <p>I have tried:</p>
  
  <pre><code>select * from (
  select * from TEST2 where tag in ('LN','SN')
  )
  PIVOT
  (
    max(value)
    for tag in ('LN','SN')
  )
  order by category,subcat,item,"Date"
  </code></pre>
  
  <p>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.</p>
  
  <p>Below is my test table with its data</p>
  
  <pre><code>  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');
  </code></pre>