[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

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

admlangford

3/16/2007 2:01:00 AM

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
5 Answers

Steve Dassin

3/16/2007 3:00:00 AM

0


<admlangford@gmail.com> wrote in message
news:1174018757.843611.198140@l77g2000hsb.googlegroups.com...
> 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

3/16/2007 3:57:00 AM

0

On 3?16?, ??10?01?, admlangf...@gmail.com wrote:
> 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

3/16/2007 4:04:00 AM

0

On 16 Mar, 02:01, admlangf...@gmail.com wrote:
> 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/librar...(en-US,SQL.90).aspx
--

admlangford

3/16/2007 4:19:00 AM

0

On Mar 16, 2:04 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote:
> On 16 Mar, 02:01, admlangf...@gmail.com wrote:
>
>
>
>
>
> > 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/librar...(en-US,SQL.90).aspx
> --- Hide quoted text -
>
> - Show quoted text -

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

admlangford

3/16/2007 4:45:00 AM

0

On Mar 16, 1:57 pm, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?16?, ??10?01?, admlangf...@gmail.com wrote:
>
>
>
>
>
> > 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