Solved

Oracle Pivot Query

Posted on 2010-11-24
9
731 Views
Last Modified: 2013-12-07
I am trying to write a pivot query in Oracle 11g.

table
cust_id          date              prod_id
10001701      25/03/2003      2
10001701      25/03/2003      1
10001701      25/03/2003      5

Output
cust_id          date              prod_id_1    prod_id_2 prod_id_3 prod_id_4 prod_id_5
10001701      25/03/2003    1                 1                0               0               1

There wont be duplicate record for prod_id againt cust_id.

0
Comment
Question by:sachin_dba
9 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34210464
select cust_id, date,
sum(case when prod_id=1 then 1 else 0 end) prod_id_1,
sum(case when prod_id=2 then 1 else 0 end) prod_id_2,
sum(case when prod_id=3 then 1 else 0 end) prod_id_3,
sum(case when prod_id=4 then 1 else 0 end) prod_id_4,
sum(case when prod_id=5 then 1 else 0 end) prod_id_5
from tbl
group by cust_id, date
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34210469
I do not have 11g at the moment.

The below links should help on how to do that easily. Let me know if you cannot do it. Give me the pivot query which you have it any and i can modify it.

http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php

thanks
0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34210472
in 11G there is an pivot option, but i am not sure how that works..

but without using any pivot option and if your columns are fixed you can do this,


select cust_id, trunc(date),  min(prd_id_1), min(prd_id_2), min(prd_id_3), min(prd_id_4), min(prd_id_5) from
select cust_id, date,
decode(prdid, 1, 1, 0) prd_id_1,
decode(prdid, 2, 1, 0) prd_id_2
decode(prdid, 3, 1, 0) prd_id_3
decode(prdid, 4, 1, 0) prd_id_4
decode(prdid, 5, 1, 0) prd_id_5 from table)
group by cust_id, trunct(date);
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34210522
Just a note, you can use the solutions given by cyberwiki if your data has a maximum of only 5 prod codes. If you have 20 prod codes, then you may have to modify the query and modify for next 15 prod codes to make it for you.

If your data has unknown number of prod codes or is dynamic, then that solution cannot work for you and you have try to use the 11g pivot operator.

Thanks
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 58

Expert Comment

by:cyberkiwi
ID: 34210570
nav_kum_v

Are you sure the 11g pivot operator can handle dynamic number of columns?
http://www.oracle-developer.net/display.php?id=506

Seems to imply same as SQL Server, following ansi standard, the IN list is fixed.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34210599
Cyberkiwi

I agree to what you said as the fact the IN list for the PIVOT has to be hard coded there and that is why i said in my previous update "you have to try to use the 11g pivot operator".

As i do not have the 11g version database, i cannot confirm whether my try can work or not. But most likely No will be answer.

Thanks,
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34210607
see this link :

We can use ANY in the IN operator or use a sub query there to get the prod codes dynamically.

http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php

0
 

Author Closing Comment

by:sachin_dba
ID: 34210687
thnaks.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34210723
@sachin_dba,

I am fine with you in accepting the answer you like. But then i do not understand why you mentioned that "I am trying to write a pivot query in Oracle 11g." while creating this quesion. This gives an impression that you need help for a pivot query in 11g but the answer which is now accepted does not use the pivot query in 11g.

Thanks
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 51
select query - oracle 16 82
Convert Oracle data into XML document 2 38
Best RAID for a BDD Oracle 4 22
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

757 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

17 Experts available now in Live!

Get 1:1 Help Now