Hi

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;


Thanks in advance

//MOL

Hi olesen,

For a higher chance of getting your question answered I would suggest
that you post DDL and INSERT INTO ... code like the following:

CREATE TABLE tbl_ORDH_STAT ( o char(10), type char(10), fab int,
AndWhateverOtherColumnsYouMayNeed char(23) )
INSERT INTO tbl_ORDH_STAT ( '1101', 'rest', 1, 'sample data' )
INSERT INTO tbl_ORDH_STAT ( '1120', 'less', 2, 'more sample data' )

Also, specify the desired results, as in:

Column1 Column2
--------- ---------
Result1C1 Result1C2
Result2C1 Result2C2

Best,
Henk

olesen schreef op 28-06-2011 08:59:

olesen (mogenso (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

On Jun 28, 2:59*am, olesen <moge... (AT) gmail (DOT) com> wrote:
The Access cross-tabs are handy since they generate column names
dynamically. Of course, it also makes them slower.. If performance
is the issue, I've had good luck creating a summary view in SQL
server, linking it to an Access database, and running the Access cross-
tab against the SQL server summary view.

Fred.