Hello,
I have sales history stored in a table in the following format:
Customer Year Sales
1 2006 5000
1 2005 8000
1 2004 2000
2 2006 6000
2 2005 10000
2 2004 20000
Can someone give me an example of a query that would output the data like
this:
Customer 2006Sales 2005Sales 2004Sales
1 5000 8000 2000
2 6000 10000 20000
Thanks in advance.
MikeThe word you're looking for here is PIVOT.
http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
information you can throw a stick at.
Rob
"Mike" wrote:
> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>|||Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
> The word you're looking for here is PIVOT.
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
> information you can throw a stick at.
> Rob
> "Mike" wrote:
>
like|||You can use case when statement in SQL Server 2000.
For example,
SELECT CustomerID,CASE Year WHEN 2006 THEN Sum(Sales) END '2006Sales'
...,
"Mike"?? ??? ??:
> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>|||Ah, that's more complicated.
One method is to do it on the client side... ;)
Another would be to write a nasty query like:
select s.customer,
'sales2006' = sum(s.sales2006),
'sales2005' = sum(s.sales2005),
'sales2004' = sum(s.sales2004)
from
(
select customer
, 'sales2006' = case when year = 2006 then sales else 0 end
, 'sales2005' = case when year = 2005 then sales else 0 end
, 'sales2004' = case when year = 2004 then sales else 0 end
from sales
) s
group by s.customer
See how this works for you...
Rob
"Mike" wrote:
> Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
> like
>
>|||Hi Rob,
With all due respect, why would you reinforce the idea that MS
has treated this subject in depth. It is superficial at best. It doesn't
even come up to the level of the Access crosstab. MS needs its
martini shaken not stirred. You do want a better drink don't you?:)
www.rac4sql.net
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
>.
> http://msdn2.microsoft.com/en-us/library/ms177410.aspx has all the
> information you can throw a stick at.
>.|||If you don't want to reinvent the wheel try Rac.
No maintenance,no code,more time to think (and play:)
www.rac4sql.net
"Mike" <mbaith@.yahoo.com> wrote in message
news:etaY4oYbGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Isn't Pivot new in 2005? Any ideas how to do this in SQL 2000?|||Alright, maybe not all the information you can throw a stick at, but
certainly enough to be able to do this type of query. And I agree that the
PIVOT functionality is still really limited because you have to provide the
values you want to pivot over. But it's much better for people to go to the
MSDN site and read through what's written there than to have me say "Here's
a
query...", don't you think?
"Steve Dassin" wrote:
> Hi Rob,
> With all due respect, why would you reinforce the idea that MS
> has treated this subject in depth. It is superficial at best. It doesn't
> even come up to the level of the Access crosstab. MS needs its
> martini shaken not stirred. You do want a better drink don't you?:)
> www.rac4sql.net
> "Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
> news:3D08D090-BA8D-4A8D-95A5-40E39F27C253@.microsoft.com...
>
>|||Hi,
I know your trying to be helpful and do respect that.My b_t_h is with MS.
I can't believe that they have the arrogance to threat a sophisticated
'enterprise'
to the shallowness of PIVOT.And it doesn't stop there.When a cheap
utility embarrasses the worlds largest software company something is wrong.
Their 2005 product should be a far superior offering.Perhaps humilation and
ridicule will pay dividends.
www.rac4sql.net
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:CE63524D-5A6A-4526-82A2-86C641498C91@.microsoft.com...
> Alright, maybe not all the information you can throw a stick at, but
> certainly enough to be able to do this type of query. And I agree that the
> PIVOT functionality is still really limited because you have to provide
the
> values you want to pivot over. But it's much better for people to go to
the
> MSDN site and read through what's written there than to have me say
"Here's a
> query...", don't you think?
> "Steve Dassin" wrote:
>|||You might want to check out my article on this subject here ::
http://vadivel.blogspot.com/2004/12...el.blogspot.com
"Mike" wrote:
> Hello,
> I have sales history stored in a table in the following format:
> Customer Year Sales
> 1 2006 5000
> 1 2005 8000
> 1 2004 2000
> 2 2006 6000
> 2 2005 10000
> 2 2004 20000
> Can someone give me an example of a query that would output the data like
> this:
> Customer 2006Sales 2005Sales 2004Sales
> 1 5000 8000 2000
> 2 6000 10000 20000
> Thanks in advance.
> Mike
>
>
