Help: Convert these Access queries to Microsoft SQL 2000 queries:

[admlangford]

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam

 

[Steve Dassin]

Hi, we are using Reporting Services MSSQL 2000.
Thanks
Adam

Cough up 60 bucks and use Rac (and avoid PMS... (Pivot Madness Syndrome) );

www.rac4sql.net

 

[xyb]

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam

select TABLEA.ATTRIBUTE1
,sum(case when Category = 1 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_1]
,sum(case when Category = 2 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_2]
,sum(case when Category = 3 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_3]from
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1

query 2 you can make it similar to this

 

[David Portas]

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam

These look like queries that implement cross-tab reports. What
reporting/presentation solution are you using for SQL Server? Most of
those tools (Reporting Services for example) will implement cross-tabs
without the need to write such queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[admlangford]

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam

These look like queries that implement cross-tab reports. What
reporting/presentation solution are you using for SQL Server? Most of
those tools (Reporting Services for example) will implement cross-tabs
without the need to write such queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--- Hide quoted text -

- Show quoted text -

Hi, we are using Reporting Services MSSQL 2000.
Thanks
Adam

 

[admlangford]

Hi, can someone please help me convert the two Access queries below to
MSSQL 2000 queries?

Note; there are only ever three categories 1,2 and 3
============================
Query 1)
TRANSFORM Sum(IIf([RESULT]="FINISHED",1,0)) AS Expr2
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];

Query 2)
TRANSFORM Count(TABLEB.RESULT) AS RESULTCOUNT
SELECT TABLEA.ATTRIBUTE1
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY) In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1
PIVOT "Category " & [CATEGORY];
============================

Thank you for any and all help
Adam

select TABLEA.ATTRIBUTE1
,sum(case when Category = 1 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_1]
,sum(case when Category = 2 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_2]
,sum(case when Category = 3 and RESULT = 'FINISHED' then 1 else 0 end)
[CATEGORY_3]from
FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ATTRIBUTE1 = TABLEB.ATTRIBUTE1
WHERE (TABLEB.CATEGORY In (1,2,3))
GROUP BY TABLEA.ATTRIBUTE1

query 2 you can make it similar to this - Hide quoted text -

- Show quoted text -

Thanks That worked perfectly