SQL Server PIVOT

Example using the PIVOT command to construct a crosstab.

-- table definition CREATE TABLE [dbo].[server] ( [server_id] [int] IDENTITY(1,1) NOT NULL, [server_name] [varchar](30) NULL, [product] [varchar](30) NULL, [data_centre] [varchar](100) NULL, [business_unit] [varchar](100) NULL, CONSTRAINT [pk_server] PRIMARY KEY NONCLUSTERED ( [server_id] ASC) ) -- use pivot to display the number of servers by product for each business unit and data centre. DECLARE @products varchar(max) ,@sql varchar(max) SELECT @products = STUFF(( SELECT DISTINCT '],[' + product FROM dbo.[server] ORDER BY '],[' + product FOR XML PATH('') ), 1, 2, '') + ']' SET @sql = ' SELECT * FROM ( SELECT product, business_unit, data_centre FROM dbo.[server] ) t PIVOT (count(product) FOR product IN(' + @products + ')) as pvt ' EXEC (@sql)

Sample Results

business_unit	data_centre	IMAGE	MySQL	NAS	SNAP	SOLR	SQL SERVER
UnitA	Datacentre1	0	10	0	0	6	5
UnitB	Datacentre1	1	3	1	0	0	9
UnitA	Datacentre2	0	7	0	1	3	6
UnitB	Datacentre2	1	1	0	0	1	2
UnitA	Datacentre3	0	2	0	0	0	2
UnitB	Datacentre3	0	1	0	0	0	4