One of the most asked questions by users when they pull data using T-SQL is whether or not there’s a way to present the values of a field they’re interested in as columns instead. The answer is of course it’s “Yes.” You can achieve this by using PIVOT relational operator if you use MSSQL 2005 or later.
Without PIVOT
USE [MyDB]; --WITHOUT PIVOT SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory WHERE [Type] = 'Desktop' GROUP BY CostCenter, [Type] UNION SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory WHERE [Type] = 'Laptop' GROUP BY CostCenter, [Type] ORDER BY CostCenter;
With PIVOT
USE [MyDB]; --WITH PIVOT SELECT CostCenter, Desktop, Laptop FROM ( SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory WHERE [Type] = 'Desktop' GROUP BY CostCenter, [Type] UNION SELECT CostCenter, [Type], COUNT(AssetTag) as Total FROM tblInventory WHERE [Type] = 'Laptop' GROUP BY CostCenter, [Type] ) t1 PIVOT (SUM(Total) FOR [Type] in ([Desktop], [Laptop])) t2 ORDER BY CostCenter;
Leave a Reply