• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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
0
Star79
Asked:
Star79
  • 4
  • 3
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
slightwv (䄆 Netminder) 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
 
sdstuberCommented:
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now