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
