26 February 2016

T-SQL: Converting rows to columns

A common task of a database developer is to convert rows to columns from a query result set.

T-SQL has the PIVOT that facilitates the process.

A simple example:

DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))

INSERT INTO @myValues (Quantity, Category)
VALUES 
 (10, 'Fruits'),
 (200, 'Vegetables'),
 (40, 'Meats')


SELECT *
FROM @myValues

SELECT [Fruits], [Vegetables], [Meats]
FROM @myValues
PIVOT
(
  MAX(Quantity)
  FOR Category IN ([Fruits], [Vegetables], [Meats])
) Piv