Splitting up a column in a query - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Splitting up a column in a query

Author

Topic

marino3d
Starting Member

18 Posts

Posted - 2011-02-28 : 09:46:55

Hey,

I got a tricky one here and maybe one of you guys can help. Say I have a table like this:

name | purchase amount | purchase date |
Joe--------20.00--------------12/1/06
Joe--------10.00--------------12/1/06
John-------10.00--------------1/15/06
Sam--------15.00--------------5/10/06
John-------20.00--------------11/1/06
Joe--------5.00---------------8/19/07
Sam--------5.00---------------10/8/07
Sam--------10.00--------------4/22/08
Sam--------15.00--------------4/22/08

and what I need is a query that will show name and the sum of purchase amount for specific years. Here is what it would look like:

name | purchase 2006 | purchase 2007 | purchase 2008
Joe 30.00 5.00 0
John 30.00 0 0
Sam 15.00 5.00 25.00

How would I go about doing that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 09:51:47

are you using SQL 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 10:06:09

[code]
select *
from (
select [name], purchase_amount, purchase_year = datepart(year, purchase_date)
from yourtbl
where purchase_date >= '2006-01-01'
and purchase_date < '2009-01-01'
) d
pivot
(
sum(purchase_amount)
for purchase_year in ([2006], [2007], [2008])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 10:14:16

that is the alias name given to the derived table

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 10:38:42

PIVOT operator is only available from SQL 2005 onwards.

You can refer to the Books On Line for further info.
http://msdn.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 12:11:02

if the purchase table does not have any column on the name, how are you going to join to the name table ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-28 : 18:33:47

[code]
select *
from (
select n.[fname], n.[lname], t.purchase_amount, purchase_year = datepart(year, t.purchase_date)
from yourtbl t
inner join name n on t.id = n.id
where t.purchase_date >= '2006-01-01'
and t.purchase_date < '2009-01-01'
) d
pivot
(
sum(purchase_amount)
for purchase_year in ([2006], [2007], [2008])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

marino3d
Starting Member

18 Posts

Posted - 2015-02-07 : 14:15:46

aa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources