Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Rewrite Access SQL (Crosstab) to MS-SQL query
Date: Tue, 28 Jun 2011 21:33:56 +0200
Organization: Erland Sommarskog
Lines: 55
Message-ID: <Xns9F12DB60FF2DYazorman@127.0.0.1>
References: <6c28f1c8-427f-48d4-ac55-86351a4dc9ea@16g2000yqy.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="14566"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1+FWj7PGo7MtSoVLFNJ0O4p"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:E2TwxU4oBzV+4bDhnhXC6RszM1U=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:483

olesen (mogenso@gmail.com) writes:
> I need help to rewrite Access SQL to MS-SQL query
> 
> TRANSFORM Count(tbl_ORDH_STAT.RECID) AS AntalOfRECID
> SELECT tbl_ORDH_STAT.O_UGE
> FROM tbl_ORDH_STAT
> WHERE (((tbl_ORDH_STAT.O_UGE) Between "1101" And "1120") AND
> ((tbl_ORDH_STAT.TYPE)<>"REST") AND ((tbl_ORDH_STAT.Fab_1)=True))
> GROUP BY tbl_ORDH_STAT.O_UGE
> PIVOT tbl_ORDH_STAT.TYPE;

Since I don't know Access, I don't know exactly what this query does, 
and therefore I cannot give you an exact translation.

But here is an example query, which lists the number of orders processed
by each employee per month:

[sql]
SELECT convert(char(6), OrderDate),
       SUM(CASE EmployeeID WHEN 1 THEN 1 ELSE 0 END) AS CntEdwards,
       SUM(CASE EmployeeID WHEN 2 THEN 1 ELSE 0 END) AS CntJenkins,
       SUM(CASE EmployeeID WHEN 3 THEN 1 ELSE 0 END) AS CntHunt,
       SUM(CASE EmployeeID WHEN 4 THEN 1 ELSE 0 END) AS CntKnockerwell,
       SUM(CASE EmployeeID WHEN 5 THEN 1 ELSE 0 END) AS CntSmith,
       SUM(CASE EmployeeID WHEN 6 THEN 1 ELSE 0 END) AS CntStweart
FROM   Orders
GROUP  BY convert(char(6), OrderDate)
[/sql]

You may notice that the column names and employee IDs are hard-coded. If
there is a seventh employee, you would need to modify the query.

This is not how TRANSFORM works, but a SELECT statement returns a 
table and a table has a known set of columns with known data types. The very 
big advantages is that SELECT statements are composable. One SELECT 
statement can be an input table to an outer SELECT statement. This 
would not be possible with TRANSFORM.

To get a dynamic crosstab with SQL Server (and most other relational engines 
for that matter) you need to generate the SELECT statement dynamically. 
I have some information about this on 
http://www.sommarskog.se/dynamic_sql.html#Crosstab, but I should add 
the warning that this is not really anything for the true novice.

(Note: There is a special PIVOT operator in T-SQL. Interesting enough,
it does not buy anything over the method above, which I find easier
to remember. And which is portable.)

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx