LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » SQL tip

SQL tip

Options

Previous Topic · Next Topic

moonsulu

Posted: Monday, March 15, 2010 10:20:05 AM

Rank: Newbie

Joined: 3/18/2009
Posts: 6
Points: 18

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?

Scott Whigham

Posted: Monday, March 15, 2010 10:29:44 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Yes, this is a "crosstab" or a "pivot table" query. You will need to either use the PIVOT() operator or write individual CASE statements. Too much to explain here but my SQL Server queries course has a lot on this exact thing:

http://www.learnitfirst.com/Course/160/Video/2369/Pivot-Tables-and-Cross-Tab-Queries.aspx

http://www.learnitfirst.com/Course/160/Video/2370/Design-Pattern-for-Pivot-Tables-and-Crosstabs.aspx

http://www.learnitfirst.com/Course/160/Video/2408/PIVOT-and-Crosstab-Queries.aspx

http://www.learnitfirst.com/Course/160/Video/2409/Design-Pattern-for-Pivot-Tables-and-Crosstabs-Two.aspx

moonsulu

Posted: Monday, March 15, 2010 11:15:18 AM

Rank: Newbie

Joined: 3/18/2009
Posts: 6
Points: 18

hi Scott Whigham, thanks very much, i will look into those. have a nice day!

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.