How to Show Field Values as Columns in SQL Server

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;

Further Reading

Using PIVOT and UNPIVOT
SQL Server 2019 Revealed: Including Big Data Clusters and Machine Learning
How to Get Table Definition in SQL Server