LISTING 4: Dynamic SQL for Variable-Width Crosstab Report
CREATE PROCEDURE procXTabDSQL
AS
SET NOCOUNT ON
Declare @StoreName varchar(40)
Declare @strSQL varchar(8000)

SET @strSQL = 'SELECT au_name as [Author Name]'

Declare XTab_Cursor CURSOR
FOR
SELECT stor_name FROM dbo.stores ORDER BY stor_name
OPEN XTab_Cursor
FETCH FROM XTab_Cursor INTO @StoreName
WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @strSQL = @strSQL + ', SUM(CASE stor_name WHEN ' +  CHAR(39) + 
	REPLACE(@StoreName, '''', '''''') + CHAR(39) + ' THEN sales ELSE 0
END) AS [' + @StoreName + ']'
    FETCH NEXT FROM XTab_Cursor INTO @StoreName
    END
CLOSE XTab_Cursor
DEALLOCATE XTab_Cursor

SET @strSQL = @strSQL + 'FROM dbo.vwSales2 GROUP BY au_name
        ORDER BY au_name '

EXECUTE(@strSQL)
RETURN