I am querying some sales figures in SQL Server to determine sales by customer by year. This is easy in Access:
TRANSFORM Sum(dbo_HISTORY.NET_SALES) AS SumOfNET_SALES
SELECT dbo_HISTORY.CUSTOMER_NAME
FROM dbo_HISTORY
GROUP BY dbo_HISTORY.CUSTOMER_NAME
PIVOT dbo_HISTORY.YEAR;
In SQL Server, I can add things easily enough:
SELECT [SPEC_MIS].DBO.HISTORY.CUSTOMER_NAME,[SPEC_MIS].DBO.HISTORY.YEAR,sum([SPEC_MIS].DBO.HISTORY.NET_SALES) AS SALES
FROM [SPEC_MIS].DBO.HISTORY
GROUP BY CUSTOMER_NAME,YEAR
ORDER BY CUSTOMER_NAME,YEAR
But I can't work out how to get the pivot to work. Note that I need this query to work on any arrangement of years, I don't want to have to rewrite this query every year.
