I came across an article in SQL Mag about Crosstab Queries. It works
great in Query Analyzer, but I'm stuck on how to use it in an Access
ADP. I need to use it as a Recordsource in a form and report. Can
someone tell me how to use it, and please try to be as descriptive as
possible. I'm new to Stored Procedures.
Thanks
*****************************************
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
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
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 c' + @key
SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
-- PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)
GO