I found this somewhere, I forget exactly where so forgive me for not giving credit.
put this in your MASTER DB
execute it as an SP passing the parms as defined.
David
--LISTING 3: Header of the sp_CrossTab Stored Procedure
CREATE PROC sp_CrossTab
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(128), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(128), -- Destination columns (on columns)
@sumcol AS sysname = NULL -- Data cells
AS
set @oncols = @ONCOLS
--LISTING 4: Step 1 of the sp_CrossTab Stored Procedure: Beginning of the SQL String
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
--LISTING 5: Step 2 of the sp_CrossTab Stored Procedure: Storing Keys in a Temp Table
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 + @NEWLINE -- For debug EXEC (@keyssql)
EXEC (@keyssql)
--LISTING 6: Step 3 of the sp_CrossTab Stored procedure: Middle Part of SQL String
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 '0' -- NULL SET TO 0 NOT 1
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key +']'
SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END
--LISTING 7: Step 4 of the sp_CrossTab Stored Procedure: End of SQL String
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
--PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)
GO
|
