Re: Shorten a long sql query

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 10:08:14 -0400

Uh, how about:

SELECT
    User,
    ServerRole,
    Count(*)
FROM
    dbo.ServerInventory
WHERE
    User > ''
GROUP BY
    User,
    ServerRole
ORDER BY
    User

This will return a row for every user<>serverRole combination. If you need
to flatten this out in any way, do so in ASP, not in the query. Then you
don't have to hardcode data values in the query logic. 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Anthony" <antgoodlife@yahoo.com> wrote in message
news:277ddeb3.0408230548.6f23e28f@posting.google.com...
> Is there a shorter way to write the following query?  I want to do it
> for each type of ServerRole and there will eventually be many types...
> not just exp/prod/q/test/etc...:
>
> select i.User, count(p.serverRole) as 'PROD', count(t.ServerRole) as
> 'Test', count(q.ServerRole) as 'Q',count(s.ServerRole) as
> 'SPARE',count(e.ServerRole) as 'EXP'
> from dbo.Serverinventory as i
>  left outer join dbo.ServerInventory as p
>    on (i.User = p.User and i.recordid = p.recordid and p.ServerRole =
> 'PROD')
>  left outer join dbo.ServerInventory as t
>    on (i.User = t.User and i.recordid = t.recordid and t.ServerRole =
> 'TEST')
>  left outer join dbo.ServerInventory as q
>    on (i.User = q.User and i.recordid = q.recordid and q.ServerRole =
> 'Q')
>  left outer join dbo.ServerInventory as s
>    on (i.User = s.User and i.recordid = s.recordid and s.ServerRole =
> 'SPARE')
>  left outer join dbo.ServerInventory as e
>    on (i.User = e.User and i.recordid = e.recordid and e.ServerRole =
> 'EXP')
> where i.User is not null and i.User <> ''
> group by i.User order by i.User
>
> Thanks in advance

Relevant Pages

  • Re: Shorten a long sql query
    ... is there a way to add a TOTAL Column for each ServerRole? ... > to flatten this out in any way, do so in ASP, not in the query. ... >> left outer join dbo.ServerInventory as p ...
    (microsoft.public.inetserver.asp.db)
  • Shorten a long sql query
    ... for each type of ServerRole and there will eventually be many types... ... 'SPARE',countas 'EXP' ... left outer join dbo.ServerInventory as p ... 'PROD') ...
    (microsoft.public.inetserver.asp.db)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)