Crosstab view - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Crosstab view

Author

Topic

ino mart
Starting Member

12 Posts

Posted - 2011-09-09 : 07:52:06

All

I have a MS-Access database which is "out of proportion size", so it is decided to move the records to MS SQL Server (which I have never been used before). Also the frontend (MS-Access) will most probably be replaced. I now need to bring over my "Access Queries" to "SQL Views", but I am stucked with the Pivots.

In Access, the SQL-code of one of such Crosstabquery is

TRANSFORM Sum(qryData.[Events]) AS [SumOfEvents]
SELECT qryData.Location, qryData.Type
FROM qryData
GROUP BY qryData.Location, qryData.Type
PIVOT qryData.[Date D-M-Y];

but what is the syntax to use in a MS SQL View? Also note the Pivot is dynamically as "Date D-M-Y" are a variable amount of days. The output is something like

Location     Type    1/Apr/2011  1/May/2011  ...  1/Sep/2011
Office 1     in          3000       5000     ...      1041
Office 1     out         1060       1500     ...      120
Office 1     rejected     100        70      ...      2065 
Office 2     in          5000       1800     ...      145
Office 2     out         1254        105     ...      10000
Office 2     rejected     420        340     ...      354

...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 07:56:39

see

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources