Solved

Oracle Pivot

Posted on 2016-07-18
8
106 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Query link database loop 8 55
Create file system directory from Oracle 10g 4 33
Read XML values 8 58
PL/SQL: ORA-00979: not a GROUP BY expression 3 56
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

730 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