If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Dynamic subquery in Pivot Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2010
Posts: 56
Dynamic subquery in Pivot Query

Hi all,

I have the following requirement:

For a table XYZ I want to have the pivot query with a subquery:

CREATE TABLE XYZ
(
a NUMBER NOT NULL,
b NUMBER NOT NULL,

f VARCHAR2(50 BYTE)

)


Insert into xyz
(a,b,f)
Values
(1, 3807,
'HE');
Insert into xyz
(a,b,f)
Values
(2, 3808, 'SHE');

Insert into xyz
(a,b,f)
Values
(3, 3809, 'WE');

Insert into xyz
(a,b,f)
Values
(4, 3807, 'FREE');


Currently I have data as:

A B F

1 3807 HE
2 3808 SHE
3 3809 WE
4 3807 FREE



Now for the above table I need data as :

A B 'HE' 'SHE' 'WE' 'FREE'

1 3807 1
3 3809 1
4 3807 1
2 3808 1


i.e. pivot for values of the F.

But I want it as a subquery or as a dynamic query for the values of column F.
I don't have to hard code the values of column F.

Currently I have the following query which gives the required output but here instead of the hard coded values of column F I want a subquery like Select F from XYZ.

Current QueryTHIS WORKS ONLY IN ORACLE 11G)
WITH pivot_data AS (
SELECT a,b,f
FROM XYZ
)
SELECT *
FROM pivot_data
PIVOT (
SUM( 1) --<-- pivot_clause
FOR f --<-- pivot_for_clause
IN ('HE','SHE','WE','FREE') --<-- pivot_in_clause
);

But I want to have the values of F given dynamically.

Please help.

Thanks and Regards,
Sudhir
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2010
Posts: 56
Sorry about the typo the output should be as follows with 1 in each row for each values of F acting as a column

A B 'HE' 'SHE' 'WE' 'FREE'

1 3807 1
3 3809 1
4 3807 1
2 3808 1

I want the same output but with dynamic generation of values of column F in table XYZ

Regards,
Sudhir
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2010
Posts: 56
I was expecting some reply on this.
Please help.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2010
Location: Pensacola, FL
Posts: 5
We have someone who needs to do something like this. Right now the plan is to create some dynamic SQL. You may find this page useful. Oracle 11g: New Pivot Table Feature | The Pythian Blog
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On