Home » SQL & PL/SQL » SQL & PL/SQL » MAtrix Query ( Complex ) (9i)
MAtrix Query ( Complex ) [message #312291] Tue, 08 April 2008 07:51 Go to next message
jhedshi
Messages: 13
Registered: February 2008
Junior Member
Hi,

there is a query which we have written :

select A.po,
MAX(CASE WHEN A.N = 1 THEN A.B_nm ELSE NULL END) AS N1,
MAX(CASE WHEN A.N = 1 THEN A.Per ELSE NULL END) AS P1,
MAX(CASE WHEN A.N = 2 THEN A.B_nm ELSE NULL END) AS N2,
MAX(CASE WHEN A.N = 2 THEN A.Per ELSE NULL END) AS P2,
MAX(CASE WHEN A.N = 3 THEN A.B_nm ELSE NULL END) AS N3,
MAX(CASE WHEN A.N = 3 THEN A.Per ELSE NULL END) AS P3,


Now when there is only 1 N1 then output is :

A.PO N1
--------------- ---------------------------------
1 F


AND if there are 3 N1's output is for 3 N1's.

We have included only 3 N3's in the query. If there is data for 5 i.e. A.N = 5 then i will have to included it in the query. Is there any way to generalize the query so that if there is
CASE WHEN A.N = 10 , it will return data for 10 automatically ?

Please help. kindly ask if more clarifications needed ..

Regards..
Re: MAtrix Query ( Complex ) [message #312293 is a reply to message #312291] Tue, 08 April 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements.
Also post the result you want with these data.

Regards
Michel
Re: MAtrix Query ( Complex ) [message #312352 is a reply to message #312291] Tue, 08 April 2008 11:22 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You will need to create the select statement dynamically. There is an example in the following thread:

http://www.orafaq.com/forum/m/18300/43710/?srch=dynamic+decode#msg_18300
Previous Topic: how to fetch first record of each group
Next Topic: Nested Tables
Goto Forum:
  


Current Time: Fri Dec 09 23:05:29 CST 2016

Total time taken to generate the page: 0.09320 seconds