Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have been banging my head against a wall at this now for a few hours. I have managed to get all the data I need down to 1 table, but I just can not figure out how to write a select statement so the output is the way I want.

Basically I have a table:

ID| Date | Cost
a  03/11   5
a  02/11   4
b  01/11   3
b  04/11   7

The column that changes the query column output is the date column. I would like to be able to group all the ID's together, with each row containing the ID and the cost of each item per month that pertains to that id, or null if there were no items that month (from jan-dec)

Output of query:

ID| JAN |FEB | MARCH | APR | MAY | JUN | JUL |......| OCT | NOV | DEC
a   NULL  4      5      NULL NULL.................................NULL
b   3    NULL   NULL     7   NULL.................................NULL

Any help in the right direction is appreciated!

Thank you.

share|improve this question
are your dates really stored as 03/11 (string) – JW 웃 Apr 5 at 2:24
Month(fieldnamehere)... that should point you in the right direction... – MaxOvrdrv Apr 5 at 2:28
@ J W, no..this was just an example @ MaxOvrdrv, I am able to determine what month a date falls under. I'm having trouble with using that information in the manner I have described. – JohnnySaxx Apr 5 at 2:34

1 Answer

up vote 2 down vote accepted 
;with data(ID, Date, Cost) as (
    select 'a', '20110311', 5 union all
    select 'a', '20110211', 4 union all
    select 'b', '20110111', 3 union all
    select 'b', '20110411', 7
)
--- the above just creates a virtual dataset named data.
--- Your query starts below here

select *
  from
    (
    select ID, left(upper(datename(month,Date)),3) Month_, Cost
    from data
    ) p
 pivot (sum(Cost) for Month_ in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) v
share|improve this answer
Thank you so much Richard, exactly what I was looking for. As a result, I have gone and done some reading on pivots. For anyone else who comes across a problem like this here is a good link: blogs.msdn.com/b/spike/archive/2009/03/03/… again, thank you Richard! – JohnnySaxx Apr 5 at 3:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.