SQL tip - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL tip

Author

Topic

ramon6969
Starting Member

11 Posts

Posted - 2010-03-15 : 11:15:11

hi guys i have this table. i've tried things but i am not skilled enough to make it to work.

date quantity
1/1/2009 1
2/1/2009 3
5/1/2009 7
9/1/2009 2
1/1/2010 4
2/1/2010 1
3/1/2010 8

and would like to have a recordset to look like this:

year jan feb mar april may jun jul aug sep oct nov dec
2009 1 3 0 0 7 0 0 0 2 0 0 0
2010 4 1 8

thanks for any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 11:31:03

[code]SELECT YEAR(date),
SUM(CASE WHEN MONTH(date)=1 THEN quantity else 0 end) as jan,
SUM(CASE WHEN MONTH(date)=2 THEN quantity else 0 end) as feb,
....
SUM(CASE WHEN MONTH(date)=12 THEN quantity else 0 end) as dec
FROM Table
GROUP BY YEAR(date)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ramon6969
Starting Member

11 Posts

Posted - 2010-03-15 : 12:12:02

hi visakh16, thanks very much, it worked!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 12:18:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ramon6969
Starting Member

11 Posts

Posted - 2010-03-17 : 13:02:30

hi visakh16,

i just have a follow up question.

how do you not show 0 on the months that are still in the future.

ex.
year j f m a m j j a s o n d
2009 1 3 0 0 7 0 0 0 2 0 0 0
2010 4 1 8

thanks once again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 13:06:37

then what do you want to show them instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ramon6969
Starting Member

11 Posts

Posted - 2010-03-17 : 15:18:29

is it possible to not show anything like in the example above? im sorry for being a pain. thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 03:31:59

Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:04:07

quote:
Originally posted by ramon6969

is it possible to not show anything like in the example above? im sorry for being a pain. thank you.

its possible provided you're doing it for report. otherwise you need to manually do this at your front end. doing this at sql end is ugly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources