Avatar of MartinNeubauer
Flag for Germany

asked on 

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
FROM Q_Project_Cost
GROUP BY Q_Project_Cost.IDProject, Q_Project_Cost.Year, Q_Project_Cost.IDQuarter, Q_Project_Cost.Qtr
PIVOT Q_Project_Cost.[Cost Type];

Microsoft AccessSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon