Select Statement
-
Hi
I have a simple table with the following fields
Customer
Sales date
Sales value
I would like to do a select statement to output into the following format
customer | sales val yr 1 | sales val yr 2 | sales val yr3
Could I do this through a sql select statement? and if so what is the correct
syntax to achieve this.
Thanks for your help!
-
Sagar, check out some of the "script" resources on this site at http://www.sqlservercentral.com/scripts/categories.asp?groupname=SQL%20Server%207.0/2000&groupid=1 either Misc. or TSQL aids, search for "pivot" etc.
Please post back to this thread if (or not) you found a satifactory answer. Thanks.
Once you understand the BITs, all the pieces come together
-
You would do it with a statement like this one:
------------------------------------------
select sum(CASE year
WHEN 2002 THEN sales
ELSE null
END) as y2002,
sum(CASE year
WHEN 2003 THEN sales
ELSE null
END) as y2003,
sum(CASE year
WHEN 2004 THEN sales
ELSE null
END) as y2004
from sales_data
------------------------------------------
If you don't have a "year" column, per se, then you could use a statement like "DATEPART(yyyy, sales_date)" to get the year that the transaction occured.
Wow - the formatting really sucks on that. I suppose that's why I do SQL and not HTML
Ryan
-
one minor fix to the above solution
select Customer, sum(CASE year
WHEN 2002 THEN sales
ELSE null
END) as y2002,
sum(CASE year
WHEN 2003 THEN sales
ELSE null
END) as y2003,
sum(CASE year
WHEN 2004 THEN sales
ELSE null
END) as y2004
from sales_data
Group By Customer
* Noel -
Thanks Guys - both proved very useful!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply