Dynamic Crosstab in SQL Server

create PROC sp_CrossTab
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(500), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(1000), -- Destination columns (on columns)
@sumcol AS sysname = NULL, -- Data cells
@whereclause as nvarchar(500)= NULL -- where clause
AS
DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END


CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

print @keyssql
EXEC (@keyssql)


DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key+ ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE
if @whereclause is not null
SET @sql = @sql+ 'WHERE ' + @whereclause + ' '+ @NEWLINE
SET @sql = @sql+ 'GROUP BY ' + @onrows + @NEWLINE
SET @sql = @sql+ 'ORDER BY ' + @onrows

PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)

---Reference SQL Magazine
----------------------------------------
Eg:

Use Northwind
go
EXEC sp_CrossTab
@table = 'Orders',
@onrows = 'MONTH(OrderDate)',
@onrowsalias = 'OrderMonth',
@oncols = 'YEAR(OrderDate)'


USE pubs
go
EXEC sp_CrossTab
@table = 'sales',
@onrows = 'stor_id',
@oncols = 'YEAR(ord_date)',
@sumcol = 'qty'