Altering Dynamic Crosstab Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Altering Dynamic Crosstab Stored Procedure

Author

Topic

THEITGUY
Starting Member

4 Posts

Posted - 2004-08-19 : 20:12:56

I got this SP code sample from winnetmag.com. It generates a dynamic crosstab query. I've tested it and it works fine. However, I was wondering if I could change it to use views instead of going directly to the table.

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)

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

EXEC (@sql)
GO

TO USE this stored procedure:

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

Any help would be appreciated.

rossmcloughlin
Starting Member

1 Post

Posted - 2004-09-21 : 09:52:55

One flaw. What happens when @sql grows to more 8000 chararcters?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources