Here's an example using some of your data in a table I created on the fly
And this for a queryCode: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)Produced this for outputCode: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
Note that the compatability level has to be set to 90 for this to work.Code:Store Battery Belt Fluid Flush ---------- ----------- ----------- ----------- ----------- Store1 8 3 85 88 Store2 11 87 120 1021 Store3 150 1010 333 6051




Reply With Quote