Oracle Pivot

Hello,
I have the below code in oracle:
 WITH pivot_data AS (
            SELECT  dp.NSN_CODE as NSN,QUANTITY,QTYDELIVERED,
       d.THISDATE as ORDER_DATE,
        dv.VENDOR_CODE,
       dv.VENDOR_NAME,
       rfp.VENDOR_SHARE_PERCENT as SPLITCOL,
       TO_CHAR(PJN.NOTE_TEXT) AS COMMENTS
       FROM FACT_PO_LINES fpo 
  INNER JOIN DIM_PRODUCT dp ON fpo.DIM_PRODUCT_ID = dp.DIM_PRODUCT_ID
INNER JOIN DIM_VENDOR dv ON fpo.DIM_VENDOR_ID = dv.DIM_VENDOR_ID
INNER JOIN DIM_DATE d ON fpo.PRINTED_DATE_ID = d.DIM_DATE_ID
INNER JOIN FACT_RFP_PRODUCT rfp ON fpo.DIM_PRODUCT_ID  = rfp.DIM_PRODUCT_ID and fpo.DIM_VENDOR_ID = rfp.DIM_VENDOR_ID
INNER JOIN FACT_PA_ITEM pai ON fpo.DIM_PRODUCT_ID  = pai.DIM_PRODUCT_ID and fpo.DIM_VENDOR_ID = pai.DIM_VENDOR_ID
INNER JOIN DIM_DATE deff ON pai.DIM_DATE_EFFECTIVE_ID = deff.DIM_DATE_ID
INNER JOIN DIM_DATE dexp ON pai.DIM_DATE_EXPIRATION_ID = dexp.DIM_DATE_ID
LEFT JOIN DIM_PJNOTE PJN ON fpo.SOURCE_POLINE_ID = PJN.SOURCE_ORDERLINE_ID
WHERE 
    fpo.DIM_PROGRAM_ID IN (SELECT DIM_PROGRAM_ID FROM DIM_PROGRAM WHERE PROGRAM_NAME LIKE 'TSI%')
    AND (DV.VENDOR_CODE  LIKE '%RET%' OR DV.VENDOR_CODE  ='BRI771')
      ORDER BY vendor_code
      
            )
   SELECT *
    FROM   pivot_data 
    PIVOT (
             sum(QUANTITY) as ORDERED_QTY,
               sum(QTYDELIVERED) as DELIVERED_QTY
           FOR vendor_code          --<-- pivot_for_clause
          IN  ('GYARET','MXARET')   --<-- pivot_in_clause
         )
  WHERE  NSN IN ('2620-00-137-0262')

Open in new window

How can I modify the above query so that instead of hardcoding ' FOR vendor_code          --<-- pivot_for_clause
          IN  ('GYARET','MXARET') ' it needs to pick up the vendor_code from the selection list
Star79Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
What do you want to do with the XML?

If you are wanting unlimited columns as output, do nothing.  As I stated in my first post:  You cannot do that without writing code.

What I was hoping to give you in the quote I provided was "unfortunately you have to know the possible values beforehand".

As XML relaxes that requirement on hard-coding the columns, it does NOT allow you to pivot unlimited columns in the result set.

With it, you can create unlimited nodes in XML.  There is no way to extract from the XML into unlimited columns.
0
 
slightwv (䄆 Netminder) Commented:
I'm guessing you want the list to be 'unlimited'?

You cannot:
This line is necessary, so unfortunately you have to know the possible values beforehand. This restriction is relaxed in the XML format of the query, described later in this article.
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

To do this dynamically, you'll need code.

I saw this a long time ago but haven't messed with it:
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
0
 
Star79Author Commented:
Thanks for the reply.I trid using the pivotxml as below:
 select * from  (
            SELECT  dp.NSN_CODE as NSN,QUANTITY,QTYDELIVERED,
       d.THISDATE as ORDER_DATE,
        dv.VENDOR_CODE,
       dv.VENDOR_NAME,
       rfp.VENDOR_SHARE_PERCENT as SPLITCOL,
       TO_CHAR(PJN.NOTE_TEXT) AS COMMENTS
       FROM FACT_PO_LINES fpo 
INNER JOIN DIM_PRODUCT dp ON fpo.DIM_PRODUCT_ID = dp.DIM_PRODUCT_ID
INNER JOIN DIM_VENDOR dv ON fpo.DIM_VENDOR_ID = dv.DIM_VENDOR_ID
INNER JOIN DIM_DATE d ON fpo.PRINTED_DATE_ID = d.DIM_DATE_ID
INNER JOIN FACT_RFP_PRODUCT rfp ON fpo.DIM_PRODUCT_ID  = rfp.DIM_PRODUCT_ID and fpo.DIM_VENDOR_ID = rfp.DIM_VENDOR_ID
INNER JOIN FACT_PA_ITEM pai ON fpo.DIM_PRODUCT_ID  = pai.DIM_PRODUCT_ID and fpo.DIM_VENDOR_ID = pai.DIM_VENDOR_ID
INNER JOIN DIM_DATE deff ON pai.DIM_DATE_EFFECTIVE_ID = deff.DIM_DATE_ID
INNER JOIN DIM_DATE dexp ON pai.DIM_DATE_EXPIRATION_ID = dexp.DIM_DATE_ID
LEFT JOIN DIM_PJNOTE PJN ON fpo.SOURCE_POLINE_ID = PJN.SOURCE_ORDERLINE_ID
WHERE 
    fpo.DIM_PROGRAM_ID IN (SELECT DIM_PROGRAM_ID FROM DIM_PROGRAM WHERE PROGRAM_NAME LIKE 'TSI%')
    AND (DV.VENDOR_CODE  LIKE '%RET%' OR DV.VENDOR_CODE  ='BRI771')
      ORDER BY vendor_code
      
            ) 
   
    pivot xml (
             sum(QUANTITY) as ORDERED_QTY,
               sum(QTYDELIVERED) as DELIVERED_QTY
           FOR vendor_code          --<-- pivot_for_clause
          IN  (select distinct vendor_code from FACT_PO_LINES fp
            INNER JOIN DIM_VENDOR dv ON fp.DIM_VENDOR_ID = dv.DIM_VENDOR_ID
            where fp.DIM_PROGRAM_ID IN (SELECT DIM_PROGRAM_ID FROM DIM_PROGRAM WHERE PROGRAM_NAME LIKE 'TSI%')
    AND (DV.VENDOR_CODE  LIKE '%RET%' OR DV.VENDOR_CODE  ='BRI771')  )   --<-- pivot_in_clause
       )
  WHERE  NSN IN ('2620-00-137-0262')

Open in new window

It gave me a column vendor_code_xml as xmltype in the result.Not sure if I implemented it the right way.Please advice
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
Did something I post have "pivot xml" or was that from a lint you Googled?

If it something you found on your own, I'll need to see the link to see what it was attempting to do.

Even if it something you found, I still think you'll need to know the number of columns at some point unless you write code.
0
 
Star79Author Commented:
Tried doing pivot xml from the link:
http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html
thats when I got column vendor_code_xml as xmltype in the result set
0
 
slightwv (䄆 Netminder) Commented:
I guess I should have scrolled farter down in the link I provided.

Using pivot XML generates an XMLTYPE as in the example.

If you are getting XML, then you likely did it correctly.
0
 
Star79Author Commented:
What needs to be done further  for the xml column,
0
 
sdstuberConnect With a Mentor Commented:
you'll need to extract the nodes of the xml into columns.

However,  that is not something you can do dynamically since you would need to know the columns to extract at parse time (i.e. before it executes) but if you can do that, then the results aren't really dynamic.

essentially, you can't do what you want purely with sql.
you can try to use an object type and pl/sql similar to what was described in the 2nd link posted by slightwv, but again, that is not a purely sql solution, since that is not possible.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.