A simple CrossTab procedure

,

this procedure gets a tableName,X axis column, Y axis column and a Value and draw a Pivot table
from the Original Table

Script also include an How to Use section

-- procedure code
CREATE PROC Sp_CrossTab
  @TableName AS sysname,
  @DistinctRow AS nvarchar(128),
  @PivotColumn AS nvarchar(128),
  @PivotData AS nvarchar(128) 
AS
/* This Procedure works with SQL2000 and SQL Server 7.0 */
DECLARE  @SQLStr varchar(8000), @COL VARCHAR(255)
CREATE TABLE #SCTTable (PIV_COLS VARCHAR(255))
INSERT INTO #SCTTable
 EXEC ('SELECT DISTINCT ' + @PivotColumn + ' FROM ' + @TableName)

SELECT @SQLStr = 'SELECT DISTINCT ' + @DistinctRow
SELECT @COL = MIN(PIV_COLS) FROM #SCTTable

  WHILE @COL <= (SELECT MAX(PIV_COLS) FROM #SCTTable)
   BEGIN
           SET @SQLStr = @SQLStr + ' , CASE WHEN (SELECT ' +  @PivotData +
                           ' FROM ' +  @TableName  + ' t2
                           WHERE  t2.' +  @DistinctRow  + ' =  t1.' +  @DistinctRow  + '
                          AND  CAST(' + @PivotColumn + ' as VARCHAR) =  ''' + cast(@COL AS
                            VARCHAR(255)) + ''' ) IS NULL THEN CHAR(32) ELSE ' + @PivotData + ' END AS '
                             + @PivotColumn + '_' + REPLACE(REPLACE(REPLACE(cast(@COL AS
                             NVARCHAR),'.','_'),CHAR(32),'_'),':','') + '_' + @PivotData
                            SELECT @COL = MIN(PIV_COLS) FROM #SCTTable WHERE PIV_COLS > @COL
   END 
SET @SQLStr = @SQLStr +  ' FROM ' + @tablename + ' t1'
--PRINT @SQLStr --for DEBUGGING
EXEC (@SQLStr)
GO

-- Example How to use:

CREATE TABLE grades  (
course char(4), student char(10), grade int )
INSERT INTO grades VALUES ('VB',  'ELI', 80)
INSERT INTO grades VALUES ('SQL', 'SHMIL', 99)
INSERT INTO grades VALUES ('VB', 'SHMIL', 69)
INSERT INTO grades VALUES ('SQL', 'ELI', 79)
GO

EXEC SP_CROSSTAB 'grades','student','course','grade'

Rate

1 (1)

Share

Share

Rate

1 (1)