You Asked
Hello,
I've spent about a week so far with no luck on this question so am trying here!
You look like the guru and have numerous interesting examples not far off from this..
I have a table:
create table t ( a int, date_field date, c varchar2(30), d int );
with this data in it:
insert into t values ( 1, to_date( '01-jan-2013'), 'hello', 0 );
insert into t values ( 2, to_date( '01-feb-2013'), 'wonderful', 10 );
insert into t values ( 3, to_date( '20-mar-2013'), 'world', 20 );
insert into t values ( 4, to_date( '21-apr-2013'), 'tree', 10 );
insert into t values ( 5, to_date( '21-jun-2013'), 'tree', 30 );
insert into t values ( 6, to_date( '01-jul-2013'), 'world', 10 );
insert into t values ( 7, to_date( '30-jul-2013'), 'world', 20 );
insert into t values ( 8, to_date( '30-jul-2013'), 'are', 30 );
insert into t values ( 9, to_date( '30-jul-2013'), 'world', 30 );
When I
select * from t where date_field == jul
I get:
a date_field c d
------------------------------
6 01-jul-2013 world 10
7 30-jul-2013 world 20
8 30-jul-2013 are 30
9 30-jul-2013 world 30
I need to get the select to output all distinct c texts on one row.
with the number days of the month in a column with the value
d added for similar date_field.
ie
c 01 02 03 04 .... 30 31
-----------------------------------------
world 10 0 0 0 .... 50 0
are 0 0 0 0 .... 30 0
Any ideas how to go about this?!
Thanks for any pointers!
and Tom said...
A query will have a fixed number of columns. Therefore, unless you want to
a) write a query using static sql
b) use the output of that query to write a dynamic sql statement (one that selects just the perfect number of columns)
you will have a fixed number of output days. We can include a column that tells you how many of the remaining columns are relevant, I think that will be easiest.
here is a query you can use, I've commented out many of the columns to make it fit, just uncomment them:
ops$tkyte%ORA11GR2> select to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd')) days, x.*
2 from ( select c, to_char( date_field, 'dd' ) day, d
3 from t
4 where date_field >= to_date( :x, 'mon-yyyy' )
5 and date_field < last_day( to_date( :x,'mon-yyyy') )+1 )
6 pivot (sum(d) for day in
7 ('01','02', /* ... '03','04', '05','06','07','08','09','10',
8 '11','12','13','14','15','16','17','18','19','20', ...
9 '21','22','23','24','25','26','27', .... */ '28','29','30','31')
10 ) x
11 order by c
12 /
DAYS C '01' '02' '28' '29' '30' '31'
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
31 are 30
31 world 10 50
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment