HowTo: Summarise Verticle Data Horizontally

1 Jul 2005 4:10 AM

Richard Myers

Hello,

Im hoping someone can help me with the following problem:

I have a table consisting of three fields (CustomerId, City, QAType) where
QAType is a discriminator with (n) possible values.
I want to run a select query that takes all the rows in the table and groups
them according to City/QAType and return a recordset with a schema that
looks like

City, QAType_n, QAType_n+1, QAType_n+2, QAType_n+3

A regular grouping can potentially return multiple rows (up to n) if a given
city has customers with all (n) QATypes but I dont want this. I want a
single summary row per city. Does anyone know how to do this please? I can
do it in code but with mulitple possible uses/variables (i.eCity/Suburb)
across an application i can see a code based effort chewing up way too much
time. There must be a way to this using T-SQL and SQL-Server?

And what do you call this sort of problem? Seems like it must be quite a
common thing to want to do.

TIA

Richard

1 Jul 2005 4:38 AM

Richard Myers

O.k forget this question. It was just knee jerk response because Outlook was
open....and im really tired, sorry.
Its a piece of cake. Its called a Pivot (doh!) and you do it something like
this:
Alter Procedure Customer_GetSummaryByCitySuburbForTerritory
(
@SalesTerritoryId int
)
As

SELECT DCity,DSuburb,
    SUM(CASE WHEN SalesCallStatus = 1 THEN 1 END) AS 'Active',
    SUM(CASE WHEN SalesCallStatus = 2 THEN 1 END) AS 'Propsective',
    SUM(CASE WHEN SalesCallStatus = 4 THEN 1 END) AS 'Cold Call'
FROM
    tblCustomer
WHERE
    SalesTerritoryId=@SalesTerritoryId GROUP BY DCity, DSuburb
return

Richard

Show quote

"Richard Myers" <n***@ofurbusiness.com> wrote in message
news:eG08qMffFHA.2424@TK2MSFTNGP09.phx.gbl...
> Hello,
>
> Im hoping someone can help me with the following problem:
>
> I have a table consisting of three fields (CustomerId, City, QAType) where
> QAType is a discriminator with (n) possible values.
> I want to run a select query that takes all the rows in the table and
groups
> them according to City/QAType and return a recordset with a schema that
> looks like
>
> City, QAType_n, QAType_n+1, QAType_n+2, QAType_n+3
>
> A regular grouping can potentially return multiple rows (up to n) if a
given
> city has customers with all (n) QATypes but I dont want this. I want a
> single summary row per city. Does anyone know how to do this please? I can
> do it in code but with mulitple possible uses/variables (i.eCity/Suburb)
> across an application i can see a code based effort chewing up way too
much
> time. There must be a way to this using T-SQL and SQL-Server?
>
> And what do you call this sort of problem? Seems like it must be quite a
> common thing to want to do.
>
> TIA
>
> Richard
>
>