Solved

Oracle Pivot

Posted on 2016-07-18
8
81 Views
Last Modified: 2016-08-01
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
Comment
Question by:Star79
  • 4
  • 3
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41717686
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
 

Author Comment

by:Star79
ID: 41717748
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41717758
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
 

Author Comment

by:Star79
ID: 41717781
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41717788
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
 

Author Comment

by:Star79
ID: 41717844
What needs to be done further  for the xml column,
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 41717897
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 41717901
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now