Dynamic Pivot Table in MS-SQL2000 (How to migrate MSAccess Pivot into MS-SQL)
I'm a beginner in SQL, but need to migrate an MSAccess tool to MS-SQL. I need to understand how it woeks so I provide this example (some details about source and target table are in attached XLS file).
Based on a table containing costs I need to have the sum of each [Cost Type] and the total of all. challenge here is, that the number of different [Cost Type]s is unknown (in attached example there are 3, but it could be more or less)
The following code from MS-Access can handle, but how to do in MSSQL2000?
Thanks in advance,
TRANSFORM Sum(Q_Project_Cost.nValue) AS SumOfnValue
SELECT Q_Project_Cost.IDProject, Q_Project_Cost.Year, Q_Project_Cost.IDQuarter, Q_Project_Cost.Qtr, Sum(Q_Project_Cost.nValue) AS Total
GROUP BY Q_Project_Cost.IDProject, Q_Project_Cost.Year, Q_Project_Cost.IDQuarter, Q_Project_Cost.Qtr
PIVOT Q_Project_Cost.[Cost Type];