SQL Query on Dates and Sum

[Chengwei Lee]

Hi all,

Sorry if this isn't the right forum to post, but I guess its the most appropriate one around here. I've to formulate SQL to do a transformation of a table into another.

I've a table like this:

  
[U]CNTR_NUM[/U]     [U]DATE[/U]     [U]TYPE[/U]
T2005001     01-Jan-05 A
T2005001     10-Feb-05 B
T2005002     01-Jan-05 C
T2005002     10-Feb-05 D

And I'd like to transform it into another table like this:

  
[U]CNTR_NUM[/U]     [U]JAN[/U]     [U]FEB[/U]  ...   [U]DEC[/U]
T2005001     1
T2005001                 1
T2005002     1
T2005002                 1

My main difficulties lies in transforming one (01) date column into 12 individual month columns and then populating the correct columns with the count value.

Any help or suggestions are greatly appreciated. Please accept my apologies if I've posted in the wrong forum.

Thanks!

[Mark Spritzler]

You are trying to do a crosstab query, which isn't pre-built in function in Oracle.

Look at AskTom which is Thomas Kytes website, and do a query, there are tons of posts about how to do a crosstab query.

Mark

[Chengwei Lee]

Mark, I didn't know what I'm trying to do is known as a crosstab query. Thanks for your help!