-- There are two methods of pivoting data in SQL and MS Access.
-- MS Access uses the TRANSFORM statement in order to pivot data, and
-- SQL uses the PIVOT statement. The following SQL PIVOT command pivots
-- the data by month such that the month columns are created for each
--- sum of the value of the Reqd variable by month.
SELECT Planner, StockCode, Description
FROM #tmp AS Result
PIVOT
(
SUM(Reqd)
FOR Mnth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT
--This is logically equivalent to:
SELECT
SUM(CASE WHEN Mnth=1 THEN Reqd END) AS [1],
SUM(CASE WHEN Mnth=2 THEN Reqd END) AS [2],
SUM(CASE WHEN Mnth=3 THEN Reqd END) AS [3],
SUM(CASE WHEN Mnth=4 THEN Reqd END) AS [4],
SUM(CASE WHEN Mnth=5 THEN Reqd END) AS [5],
SUM(CASE WHEN Mnth=6 THEN Reqd END) AS [6],
SUM(CASE WHEN Mnth=7 THEN Reqd END) AS [7],
SUM(CASE WHEN Mnth=8 THEN Reqd END) AS [8],
SUM(CASE WHEN Mnth=9 THEN Reqd END) AS [9],
SUM(CASE WHEN Mnth=10 THEN Reqd END) AS [10],
SUM(CASE WHEN Mnth=11 THEN Reqd END) AS [11],
SUM(CASE WHEN Mnth=12 THEN Reqd END) AS [12]
FROM #tmp A
GROUP BY Planner, StockCode,Description
ORDER BY Planner,StockCode,Description