Pivot Question

I want to pivot some data returned from a query. I've use the standard
technique [Sum(Case...)] below in the past, but in this situation it's not
ideal.
Typical solution:
SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
In my case, I'm listing the Parts that need despatching and how many are
needed of each. The first issue is that there are 30-50 parts typically, and
I don't think that 30-50 Sum(Case...) statements are the best solution.
Secondly (and more importantly), new parts are added and removed on a
regular basis, so hard-coding like this is pure folly.
What are the best alternatives to this? Are there any?
I have a way around this problem using my ASP code, but I'm interested in a
general solution for such a scenario for future purposes as well.
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Am I missing something.
Grouping on part name or number seems the obvious solution.|||Am I missing something.
Grouping on part name or number seems the obvious solution.|||"andy" <aon14@.lycos.co.uk> wrote in message
news:1134388670.211751.172940@.o13g2000cwo.googlegroups.com...
> Am I missing something.
Either of us could be...

> Grouping on part name or number seems the obvious solution.
>
Yeah that gets me so far...
PartNo - Count:
Part1 - 10
Part2 - 20
Part3 - 15
Whereas I want:
Part1 - Part2 - Part3
10 - 20 - 15
This is a bit of a contrived example since the solution is easy enough to
achieve in my ASP code, but I've had examples in the past where the ASP
solution wasnt appropriate...
CJM|||The best solution is the one you've already cited; do it on the client
side. Your server will love you for it :)
However, if you must do it on the server side, google for "SQL Server
dynamic pivot" for various solutions to this problem. Most have
limitations, but they can do the trick. As an aside, SQL Server 2005
has a PIVOT command built-in.|||Hi,
Why not try a third party product
AGS Crosstab Builder for SQL 2000
www.ag-software.com
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
>I want to pivot some data returned from a query. I've use the standard
>technique [Sum(Case...)] below in the past, but in this situation it's not
>ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
> and I don't think that 30-50 Sum(Case...) statements are the best
> solution. Secondly (and more importantly), new parts are added and removed
> on a regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
> a general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||There is no way to dynamically create a pivot (even in 2k5 iirc)
Unless you goto the extremes and create dynamic sql statements and use
EXEC( ... ) - which is generally very hard to maintain and has performance
impacts.
Your best bet is to return the data to the client as a two field dataset and
cross tab it in the front end.
Part Number
-- --
KSTX 1345
QUZR 45
38XJ 8723
9MSU 1437
SM5J 127
MABV 731
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
> I want to pivot some data returned from a query. I've use the standard
> technique [Sum(Case...)] below in the past, but in this situation it's not
> ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
and
> I don't think that 30-50 Sum(Case...) statements are the best solution.
> Secondly (and more importantly), new parts are added and removed on a
> regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
a
> general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> However, if you must do it on the server side, google for "SQL Server
> dynamic pivot" for various solutions to this problem. Most have
> limitations, but they can do the trick. As an aside, SQL Server 2005
> has a PIVOT command built-in.
>
One thing hampering my search via google is the proliferation of help on SQL
2005!
Still... should be migrating across soon...|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> ... SQL Server 2005 has a PIVOT command built-in.
She loves me...she loves me not...:)
For dynamic xtabs and other type of pivoting problems on the server side
check out RAC @.
www.rac4sql.net