Solved

Oracle Pivot

Posted on 2016-07-18
8
101 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 77

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 77

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

 

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
 
LVL 77

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 77

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 74

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Row_number in SQL 6 34
sum of columns in a row in oracle 3 33
oracle collections 2 22
subtr returning incorrect value 8 31
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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