Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #483

Re: Rewrite Access SQL (Crosstab) to MS-SQL query

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Rewrite Access SQL (Crosstab) to MS-SQL query olesen <mogenso@gmail.com> - 2011-06-27 23:59 -0700
  Re: Rewrite Access SQL (Crosstab) to MS-SQL query Henk van den Berg <me@myplace.net> - 2011-06-28 09:09 +0200
  Re: Rewrite Access SQL (Crosstab) to MS-SQL query Erland Sommarskog <esquel@sommarskog.se> - 2011-06-28 21:33 +0200
  Re: Rewrite Access SQL (Crosstab) to MS-SQL query "Fred." <ghrno-google@yahoo.com> - 2011-06-30 16:09 -0700

csiph-web