I am working out of our sales table in DB and trying to put together a query that returns the following:
Customer 2010 Sales 2011 Sales 2012 Sales
Each customer may have any number of sales in a given year. I have it set up currently that it provides three rows per customer, with a different years sales in each row (using SUM() and GROUP BY YEAR()) but this is more difficult for comparison.
Is there any way to create a single query which returns aggregates in multiple columns per row?
My current code:
SUM(ART.SalesAnalysis)AS 'Sales', YEAR(PostingDate) AS 'Year'
FROM ARTransaction AS ART
JOIN Customer AS Cu
ON ART.CustomerN = Cu.CustomerN
WHERE YEAR(PostingDate) >= '2010'
AND YEAR(PostingDate) <= '2012'
AND Cu.SalespersonN <> 0
GROUP BY Cu.SalespersonN, Cu.CustomerName, YEAR(PostingDate)
ORDER By Cu.CustomerName, YEAR(PostingDate)