-
data required in pyramid format
Hi,
I have a table having below data.Each row_id is having a date associated with it.
table structure:
create table test_pyramid
(dated date,
count number,
row_id number
)
DATED COUNT ROW_ID
07/02/2008 129 1
07/03/2008 130 1
07/04/2008 131 1
07/05/2008 132 1
07/06/2008 133 1
07/03/2008 1090 2
07/04/2008 1091 2
07/05/2008 1092 2
07/06/2008 1093 2
07/04/2008 591 3
07/05/2008 592 3
07/06/2008 593 3
I want to display the data in the below pyramid format.
DATED COUNT COUNT_1 COUNT_2
07/02/2008 129
07/03/2008 130 1090
07/04/2008 131 1091 591
07/05/2008 132 1092 592
07/06/2008 133 1093 593
I used below query to get the format but the problem is row_id can increase upto 60 and so will the dated entry.
I need a dynamic query to create such pyramid.
select a.dated,a.count,b.count,c.count FROM TEST_PYRAMID a,
(select dated ,count FROM TEST_PYRAMID where row_id=2) b,
(select dated ,count FROM TEST_PYRAMID where row_id=3) c
where a.dated=b.dated(+)
and b.dated=c.dated(+)
and a.row_id=1
order by 1
-
why not resort to the old-and-good pivot table solution?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
The row_id is not actuall fixed to 3 .It can increase upto 60.i.e columns can increase upto 60.
So how to write dynamic pivot query?
thanks
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|