Access crosstab query conversion in SQL Server 2000
-
Hello Friends,
I am new here and to SQL Server I have created a client/server application with Access in the frontend and SQL Server in the backend. Some of my queries did not upsized correctly and I dont know how to make them work. Someone told me to write this query as a stored procedure but I am a beginner so please help me converting this query to stored procedure.
TRANSFORM First([Messung_Ottensen].[Temperatur]) AS [SummeVonMessung_Ottensen_NO2_N Nitritstickstoff]
SELECT (Format([Dat],"dd mmm yyyy")) AS Ausdr1
FROM Messung1
WHERE (((Messung1.Dat)>#9/1/2003#) AND ((Messung1.Messung_Ottensen.Projekt)="ottensen"))
GROUP BY (Year([Dat])*12+Month([Dat])-1), Messung1.Dat, (Format([Dat],"dd mmm yyyy"))
PIVOT Messung1.Messung_Ottensen.Projekt;
-
Unfortunately, SQL Server 2000 has no provision for cross-tab queries. You can still return a pivoted data set but you need to know all of the possible values in the pivot column. It would look something like this...
SELECT Date,
SUM(CASE WHEN Project = 1 THEN Temperature END) AS Project1,
SUM(CASE WHEN Project = 2 THEN Temperature END) AS Project2,
SUM(CASE WHEN Project = 3 THEN Temperature END) AS Project3
FROM Message1
GROUP BY Date
Where Temperature is the data field, Project is the Column field, and Date is a Row field.
Hope this helps.
Aranda
-
oops, this was a copy of the previous reply... posted twice due to an error in the ASP.NET application.
-
This might work for you...
It's a crosstab tool for SQL Server that was designed with Access functionality in mind.
-
This was a duplicate post. I guess it actually saved the message before it timed out and crashed.
-
This was a duplicate post. I guess it actually saved the message before it timed out and crashed.
-
With the select statement, i have tried but somehow it is not working.
-
Check out the RAC utility.It's similiar to Access crosstab but much more powerful.No messy sql coding required
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply