Double Pivot

Hello everybody,
I used to have a cross-tab query which gives me the number of orders
and the total value per year for each customer in the northwind
database. The result looks like this:
Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
Now I try to rewrite it using the new PIVOT operator. While I succeed
in having the count per year or the sum, I can't figure out how to
combine both aggregates in one query. BOL online aren't much help and
so far in all the articles I haven't seen an example using more than
one.
Does anybody knows if this is possible ?
Thanks MarkusIt is not possible :(
Send an e-mail to sqlwish@.microsoft.com ...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||m.bo...@.quest-consultants.com wrote:
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
Join the 2 simpler pivot queries?|||Check out the RAC utility for all kinds of static/dynamic
pivoting made easy.
www.rac4sql.net
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||> Join the 2 simpler pivot queries?
Yes that's an option, but I'm afraid that my final query won't be any
shorter than the original one using CASE statements. And I don't think
it's such an unusual request to have two aggregates (or more) in a
cross-tab report.
>Check out the RAC utility for all kinds of static/dynamic pivoting made easy.[/colo
r]
I checked it out some years ago and wasn't too impressed with it back
then. Also I don't need these kind of things too often, I just created
this query as an example/exercise during classes. But maybe it's time
to have another look at RAC, since there should be a newer version by
now.
Thanks for the comments anyway.
Markus|||<m.bohse@.quest-consultants.com> wrote in message
news:1131528859.873667.16660@.g14g2000cwa.googlegroups.com...
> then. Also I don't need these kind of things too often, I just created
> this query as an example/exercise during classes. But maybe it's time
I have taken the PIVOT slides out of my T-SQL enhancements for SQL
Server 2005 talk. The three groups I showed it to all ended up asking, "can
it do (multiple aggregations, dynamic columns, etc)" -- all things that
would make perfect sense. And the answer in every case was, "no... I guess
it's not really that useful yet... but MS tells me that it WILL BE in a
future version!" So perhaps it will get a place in my T-SQL enhancements
for SQL Server 200x talk :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> I have taken the PIVOT slides out of my T-SQL enhancements for SQL
> Server 2005 talk. The three groups I showed it to all ended up asking, "c
an
> it do (multiple aggregations, dynamic columns, etc)" -- all things that
> would make perfect sense. And the answer in every case was, "no... I gues
s
> it's not really that useful yet... but MS tells me that it WILL BE in a
> future version!" So perhaps it will get a place in my T-SQL enhancements
> for SQL Server 200x talk :)
Here is a solution, which I shamelessly plug in from my book
(therefore, the lecturing tone:-)
SQL Server 2005 introduced pivot operator as syntax extension for
table expression in the from clause
select * from
(Sales pivot (sum(Amount) for Month in ('Jan', 'Feb',
'Mar'))
As soon as a new feature is introduced people start wondering if it can
accommodate more complex cases. For example, can we do two aggregations
at once? Given the Sales relation, can we output the sales total
amounts together with sales counts like this
Product JanCnt FebCnt MarCnt JanSum FebSum MarSum
Shorts 1 1 1 20 30 50
Jeans 1 1 1 25 32 37
T-shirt 1 1 10 15
We had to change column names in order to accommodate extra columns
and, if nothing else, the changed column names should hint the
solution. The other idea, which should be immediately obvious from the
way the table columns are arranged in the display, is that the result
is a join between the two primitive pivot queries
Product JanCnt FebCnt MarCnt
Shorts 1 1 1
Jeans 1 1 1
T-shirt 1 1
and
Product JanSum FebSum MarSum
Shorts 20 30 50
Jeans 25 32 37
T-shirt 10 15
Well, what about those fancy column names? There is nothing like JanCnt
in the original data. Indeed, there isn't, but transforming the month
column data into the new column with Cnt postfix is just a string
concatenation. Therefore, the answer to the problem is
select scount.*, ssum.* from (
select * from (
(select product, month || 'Cnt', amount from Sales)
pivot (count(*) for Month in ('JanCnt', 'FebCnt',
'MarCnt')
) scount, (
select * from (
(select product, month || 'Sum', amount from Sales)
pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
'MarSum')
) ssum
where scount.product = ssum.product|||"Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Here is a solution, which I shamelessly plug in from my book
Are you the same Vadim Tropashko who works for Oracle Corp?
If so, why are you writing a SQL Server book? :)

> select scount.*, ssum.* from (
> select * from (
> (select product, month || 'Cnt', amount from Sales)
> pivot (count(*) for Month in ('JanCnt', 'FebCnt',
> 'MarCnt')
> ) scount, (
> select * from (
> (select product, month || 'Sum', amount from Sales)
> pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
> 'MarSum')
> ) ssum
> where scount.product = ssum.product
That's grossly inefficient compared with using SUM(CASE) and COUNT(CASE)
and grouping on the product.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> "Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
> news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Are you the same Vadim Tropashko who works for Oracle Corp?
> If so, why are you writing a SQL Server book? :)
SQL book, not Server:-)

> That's grossly inefficient compared with using SUM(CASE) and COUNT(CAS
E)
> and grouping on the product.
This is true. Although, I fail to see the point of langauge extensions
that can't work seamlessly with the existing features without being
forced to complicate syntax every time a new, slight variation of the
problem arrives. How about pivoting on composite columns, say Month x
Day. Does it require new extension...)