Re: SQL 2005 Pivot Command
Post #3 in this thread
http://www.vbforums.com/showthread.php?t=380853
which is the sticky at the top of the db forum here - has an example of a PIVOT command that I gave an example of.
Study the difference between the old method of CASE/SUM and the new method and hopefully it will make sense.
If you give us some same data from your table and what you expect for output we could possibly help with your query specifically. But with the query you showed I have no idea what your output goal is.
Re: SQL 2005 Pivot Command
Here is my query:
vb Code:
SELECT
vTickets.StoreID,
vServiceClasses.Description,
Count(vServiceClasses.Description) as QTY
FROM
vServiceClasses RIGHT OUTER JOIN
vServices AS vServices ON
vServiceClasses.ServiceClassID =
vServices.ServiceClassID RIGHT OUTER JOIN
vTickets AS vTickets LEFT OUTER JOIN
vTicketItems AS vTicketItems ON
vTickets.TicketID = vTicketItems.TicketID ON
vServices.ServiceCode = vTicketItems.ServiceCode
WHERE
(vTickets.SaleDate BETWEEN '2007-08-05' AND '2007-8-11') AND
(vTickets.IsDeleted = 0) AND (vTickets.TicketStatus = 'Complete' OR
vTickets.TicketStatus = 'Backorder') AND
(vTicketItems.IsDeleted = 0) AND
(vTicketItems.SystemItemType = 'Service') AND
(vServices.ServiceCode = 'BATTERY' OR
vServices.ServiceCode = 'SERPENTINE BELT' OR
vServices.ServiceCode = 'ENGINE FLUSH' OR
vServices.ServiceCode = 'POWER STEER FLUID')
GROUP BY vTickets.StoreID, vServiceClasses.Description
ORDER BY vTickets.StoreID, vServiceClasses.Description
I get results like this:
Store1 Engine Flush 3
Store1 Serpentine Belts 3
Store1 Batteries 3
Store1 Power Steering Fluid 3
What I need it to show is this:
Store1 Engine Flush 3 Serpentine Belts 3 .
Re: SQL 2005 Pivot Command
Here's an example using some of your data in a table I created on the fly
Code:
Exec sp_dbcmptlevel StuFiles,90
Go
Set NoCount On
Declare @TestTbl Table (Store varchar(10),Class varchar(10),ItemCount int)
Insert into @TestTbl values ('Store1','Battery',5)
Insert into @TestTbl values ('Store1','Battery',3)
Insert into @TestTbl values ('Store2','Battery',1)
Insert into @TestTbl values ('Store2','Battery',10)
Insert into @TestTbl values ('Store3','Battery',50)
Insert into @TestTbl values ('Store3','Battery',100)
Insert into @TestTbl values ('Store1','Belt',2)
Insert into @TestTbl values ('Store1','Belt',1)
Insert into @TestTbl values ('Store2','Belt',7)
Insert into @TestTbl values ('Store2','Belt',80)
Insert into @TestTbl values ('Store3','Belt',10)
Insert into @TestTbl values ('Store3','Belt',1000)
Insert into @TestTbl values ('Store1','Fluid',50)
Insert into @TestTbl values ('Store1','Fluid',35)
Insert into @TestTbl values ('Store2','Fluid',15)
Insert into @TestTbl values ('Store2','Fluid',105)
Insert into @TestTbl values ('Store3','Fluid',111)
Insert into @TestTbl values ('Store3','Fluid',222)
Insert into @TestTbl values ('Store1','Flush',55)
Insert into @TestTbl values ('Store1','Flush',33)
Insert into @TestTbl values ('Store2','Flush',11)
Insert into @TestTbl values ('Store2','Flush',1010)
Insert into @TestTbl values ('Store3','Flush',5050)
Insert into @TestTbl values ('Store3','Flush',1001)
And this for a query
Code:
Select Store,[Battery],[Belt],[Fluid],[Flush]
From
(select ItemCount,Class,Store From @TestTbl) a
Pivot (Sum (ItemCount) For Class in ([Battery],[Belt],[Fluid],[Flush])) as Pvt
Order by Store
Produced this for output
Code:
Store Battery Belt Fluid Flush
---------- ----------- ----------- ----------- -----------
Store1 8 3 85 88
Store2 11 87 120 1021
Store3 150 1010 333 6051
Note that the compatability level has to be set to 90 for this to work.