Home » SQL & PL/SQL » SQL & PL/SQL » Query giving tabelar representation
Query giving tabelar representation [message #236209] Wed, 09 May 2007 05:23 Go to next message
sbrbot
Messages: 5
Registered: August 2006
Location: Croatia
Junior Member

There is table with the following data:

CYCLE  PRODUCT QTY
200701 PROD_A  5
200701 PROD_B  8
200702 PROD_A  11
200702 PROD_B  4
200702 PROD_C  6


The question is how to create tabelar representation of data:

CYCLE  PROD_A PROD_B PROD_C ...
200701 5      8      0
200702 11     4      6


I know how to create this query if I know fix number of products where I create CASE WHEN clause for each product. But how to create this query if one does not know fix number of products?
Re: Query giving tabelar representation [message #236223 is a reply to message #236209] Wed, 09 May 2007 06:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script:
WITH yourtable AS
(
  SELECT 200701 cycle, 'PROD_A' product, 5  qty FROM dual UNION ALL
  SELECT 200701 cycle, 'PROD_B' product, 8  qty FROM dual UNION ALL
  SELECT 200702 cycle, 'PROD_A' product, 11 qty FROM dual UNION ALL
  SELECT 200702 cycle, 'PROD_B' product, 4  qty FROM dual UNION ALL
  SELECT 200702 cycle, 'PROD_C' product, 6  qty FROM dual
)
SELECT DISTINCT
       cycle
     , prod_a
     , prod_b
     , prod_c  
FROM yourtable
MODEL IGNORE NAV
  DIMENSION BY (cycle, product)
  MEASURES (qty, 0 PROD_A, 0 PROD_B, 0 PROD_C)
  RULES ( PROD_A[any,any] = qty[cv(),'PROD_A']
        , PROD_B[any,any] = qty[cv(),'PROD_B']
        , PROD_C[any,any] = qty[cv(),'PROD_C']
        )
ORDER BY cycle        
/

The run:
SQL> @orafaq

     CYCLE     PROD_A     PROD_B     PROD_C
---------- ---------- ---------- ----------
    200701          5          8          0
    200702         11          4          6


MHE
Re: Query giving tabelar representation [message #236225 is a reply to message #236209] Wed, 09 May 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/79505/228960/102589/#msg_228960

Regards
Michel
Re: Query giving tabelar representation [message #236234 is a reply to message #236223] Wed, 09 May 2007 06:46 Go to previous messageGo to next message
sbrbot
Messages: 5
Registered: August 2006
Location: Croatia
Junior Member

But this OLAP query has PROD_A, PROD_B and PROD_C hardcoded. I'd like to have this query dynamically create columns (dinamically as many columns as many products or product codes in table).
Re: Query giving tabelar representation [message #236238 is a reply to message #236234] Wed, 09 May 2007 06:51 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So read the link I posted.

Regards
Michel
Previous Topic: gv$session vs v$session
Next Topic: no entry in dba_jobs_running
Goto Forum:
  


Current Time: Tue Dec 06 16:21:22 CST 2016

Total time taken to generate the page: 0.18484 seconds