SQL 2005 Pivot Command

[FastEddie]

How do I use the Pivot Command in SQL Server 2005. I have been looking at samples but I cannot Figure out what I am supposed to do with the two columns in the IN. I marked them as [...], [...] below.

SELECT [0], [1]
FROM
    SELECT     ServiceClassID, ServiceClassCode
    FROM         vServiceClasses
PIVOT
(COUNT (ServiceClassID)
FOR ServiceClassCode IN 
([0], [1] 
) AS pvt

[szlamany]

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.

[FastEddie]

Here is my query:

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 .

[szlamany]

Here's an example using some of your data in a table I created on the fly

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

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

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.