Skip to Main Content
  • Questions
  • select query that creates columns based on day of month with count

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, The.

Asked: October 09, 2013 - 9:43 am UTC

Last updated: October 10, 2013 - 1:40 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

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

Comments

perfect!

The HCD, October 10, 2013 - 8:29 am UTC

Thanks yes, this works as expected!

Just wondering how to default the empty entries to zero...

Thanks again!

Tom Kyte
October 10, 2013 - 1:40 pm UTC

select nvl( "'01'", 0 ), ........