Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot Query Expand / Collapse
Author
Message
Posted Monday, December 31, 2012 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:26 AM
Points: 9, Visits: 25
I just don't get it

Here's some sample data:-

create table SalesFigures 
(Id int, SalesMan varchar(20), Product varchar(20), NbrSales int, SalesValue money)

insert into SalesFigures values(1, 'George', 'WidgetA', 12, 120.00)
insert into SalesFigures values(2, 'Paul', 'WidgetA', 5, 50.00)
insert into SalesFigures values(3, 'Bill', 'WidgetA', 1, 10.00)
insert into SalesFigures values(4, 'Bill', 'WidgetB', 1, 100.00)
insert into SalesFigures values(5, 'John', 'WidgetA', 25, 250.00)
insert into SalesFigures values(6, 'Ringo','WidgetB', 1, 100.00)
insert into SalesFigures values(7, 'Paul', 'WidgetB', 10, 1000.00)
insert into SalesFigures values(8, 'George','WidgetB', 1, 100.00)
insert into SalesFigures values(9, 'George','WidgetB', 1, 100.00)
insert into SalesFigures values(10, 'Ringo','WidgetA', 1, 10.00)
insert into SalesFigures values(11, 'Ringo','WidgetA', 2, 2.00)
insert into SalesFigures values(12, 'George','WidgetB', 3, 300.00)

Using a sql2000 approach this would be my query :-
Select 
SalesMan,
Sum(Case When Product = 'WidgetA' Then NbrSales End) As 'WidgetA Sales',
Sum(Case When Product = 'WidgetA' Then SalesValue End) As 'WidgetA Value',
Sum(Case When Product = 'WidgetB' Then NbrSales End) As 'WidgetB Sales',
Sum(Case When Product = 'WidgetB' Then SalesValue End) As 'WidgetB Value'
From
SalesFigures
Group By
Salesman

This gives a nice summary of salesman's performance. that looks like:-

SalesMan WidgetA Value   WidgetB Value
Bill 1 10.00 1 100.00
George 12 120.00 5 500.00
John 25 250.00 NULL NULL
Paul 5 50.00 10 1000.00
Ringo 3 12.00 1 100.00

Sorry about the formatting, it seems to be hard to post a results grid in a sensible manner. Anyway, the question is, how would I achieve the same result using PIVOT?

Thanks.
Post #1401415
Posted Monday, December 31, 2012 9:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 4,049, Visits: 9,213
I don't get it. What are your expected results?


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1401420
Posted Monday, December 31, 2012 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:26 AM
Points: 9, Visits: 25
If the point of PIVOT is to help turn row based values into column based values, how do I use it to produce the same results as the approach using the CASE statements?

I'm just struggling to see the point of PIVOT - the syntax is absolutely horrible and confusing.
Post #1401423
Posted Monday, December 31, 2012 10:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 4,049, Visits: 9,213
:O) (12/31/2012)
If the point of PIVOT is to help turn row based values into column based values, how do I use it to produce the same results as the approach using the CASE statements?

I'm just struggling to see the point of PIVOT - the syntax is absolutely horrible and confusing.


I agree with you, I don't like the PIVOT clause either.
This article can help you to clarify a little bit the differences between the two methods.Cross Tabs & PIVOT



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1401429
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse