alternative for cross tab queries in MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

alternative for cross tab queries in MS Access

Author

Topic

aneeshmm
Starting Member

20 Posts

Posted - 2008-12-08 : 04:40:42

I have a query in MS Access. Can Anyone please tell the alternative query in Sql Server. My query is :

TRANSFORM Sum(ReportsDataTable.Estimate) AS SumOfEstimate
SELECT ReportsDataTable.ItemCode, Sum(ReportsDataTable.Estimate) AS [Project total]
FROM ReportsDataTable
GROUP BY ReportsDataTable.ItemCode
PIVOT ReportsDataTable.RoomName;

pls help ASAP. Thnx in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 05:14:43

[code]SELECT ItemCode,
SUM(CASE WHEN RoomName = 'abc' THEN Estimate ELSE 0 END ) AS [abc],
SUM(CASE WHEN RoomName = 'def' THEN Estimate ELSE 0 END ) AS [def]
FROM ReportsDataTable
GROUP BY ItemCode[/code]

E 12°55'05.63"
N 56°04'39.26"

aneeshmm
Starting Member

20 Posts

Posted - 2008-12-08 : 05:24:15

Thnx for the reply. But i have lot of room names in the database and can be added from the project too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 05:40:45

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources