Technical Article

XTAB - CROSSTAB UTILITY

,

sp_XTAB (prototype) produces pivoted (crosstab) views/output with a minimum of fuss.

No messing with code, just tell XTAB what you want to pivot.

Tested to 764 pivoted columns, XTAB can generate up to 63000 characters of dynamic sql.


Example (deliberately over-simplified)

----------------------updated 27 January 2004

Use Northwind
EXEC sp_XTAB
'Northwind',
'xtEMPL_COUNTRY',
'Orders',
'EmployeeID',
'ShipCountry',
'',
'OrderID',
'COUNT',
'NULL',
'',
'SELECT xtEMPL_COUNTRY.* FROM xtEMPL_COUNTRY'

-- Include optional parameters as '', when not required.
-- Change NULL to 0 (zero) when using SUM etc.

-- XTAB code is not intended for modification and all sql manipulation should be done through the interfacing parameters.
-- XTAB is designed for easy creation of ad-hoc pivoted views/outputs.
-- XTAB is designed for end-user querying through other procedures and/or application interfaces.
-- XTAB can generate up to 63000 characters of dynamic sql which should be more than sufficient for most user-generated crosstab queries.
-- Allowing end-users to select their own field combinations and filters means you don't have to spend hours writing single-purpose queries.
-- Ian Smith 27 Jan 2004

CREATE PROCEDURE dbo.sp_XTAB

@MYDBO varchar(255),  -- database name
@MYQRY varchar(255),  -- view (name) to create/replace
@MYTBL varchar(4000),  -- table name or join statement
@MYOUT varchar(255), -- column/s to group
@MYPIV varchar(255),  -- column/s to pivot
@MYWHR1 varchar(4000), -- WHERE clause limits fields to be pivoted (---OPTIONAL---)
@MYFLD varchar(255),  -- field/s to calculate and populate pivoted columns I.e. field + field
@MYSUM varchar(255), -- calculation method I.e. COUNT SUM AVG  etc.
@MYALT varchar(255), -- NULL or 0 (zero) I.e. NULL goes with COUNT/Character fields
@MYWHR2 varchar(4000), -- WHERE clause filters calculated field/s (---OPTIONAL---)
@MYSTP varchar(8000) -- Output or other statement (---OPTIONAL---)

 AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MYSTR varchar(8000), @MYTBLB varchar(4000), @MYPIVB varchar(4000)

-- Replace apostrophes with " | "  to avoid scoping errors which would be created by sql-parser when @MYSTR is submitted for execution.
SELECT @MYTBLB = REPLACE(@MYTBL,CHAR(39),CHAR(124))
SELECT @MYOUT = REPLACE(@MYOUT,CHAR(39),CHAR(124))
SELECT @MYPIVB = REPLACE(@MYPIV,CHAR(39),CHAR(124))
SELECT @MYWHR2 = REPLACE(@MYWHR2,CHAR(39),CHAR(124))
SELECT @MYALT = REPLACE(@MYALT,CHAR(39),CHAR(124))
SELECT @MYSTP = REPLACE(@MYSTP,CHAR(39),CHAR(124))
--  " | " symbols are put back to apostrophes before sub-strings are submitted for execution.

-- Build @MYSTR with secondary execution code.
SELECT @MYSTR = 'DECLARE @MYSUBSTR varchar(8000), @MYSUBSTR1 varchar(8000), @MYSUBSTR2 varchar(8000), @MYSUBSTR3 

varchar(8000), @MYSUBSTR4 varchar(8000), @MYSUBSTR5 varchar(8000), @MYSUBSTR6 varchar(8000), @MYSUBSTR7 varchar(8000), 

@MYSUBSTR8 varchar(8000), @MYSUBSTR9 varchar(8000), @MYSUBEND varchar(8000), @MYSUBQRY varchar(255), @MYSUBTBL 

varchar(4000), @MYSUBOUT varchar(255), @MYSUBPIV varchar(255), @MYSUBFLD varchar(255), @MYSUBSUM varchar(255), @MYSUBALT 

varchar(255), @MYXCOL varchar(255) , @MYSUBWHR2 varchar(4000), @MYSUBSTP varchar(8000), @MYSUBINT As int, @MYSUBMAX int
Use '+ @MYDBO + '
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = ''' + @MYQRY + ''')
DROP VIEW ' + @MYQRY

-- The second execution must declare its own set of variables and these are set for initialisation here.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBQRY = ''' + @MYQRY + '''
SELECT @MYSUBTBL = ''' + @MYTBLB + '''
SELECT @MYSUBOUT = ''' + @MYOUT + '''
SELECT @MYSUBPIV = ''' + @MYPIVB + '''
SELECT @MYSUBFLD = ''' + @MYFLD + '''
SELECT @MYSUBSUM = ''' + @MYSUM + '''
SELECT @MYSUBALT = ''' + @MYALT + '''
SELECT @MYSUBSTR1 = '''+CHAR(59)+'''
SELECT @MYSUBSTR2 = '''+CHAR(59)+'''
SELECT @MYSUBSTR3 = '''+CHAR(59)+'''
SELECT @MYSUBSTR4 = '''+CHAR(59)+'''
SELECT @MYSUBSTR5 = '''+CHAR(59)+'''
SELECT @MYSUBSTR6 = '''+CHAR(59)+'''
SELECT @MYSUBSTR7 = '''+CHAR(59)+'''
SELECT @MYSUBSTR8 = '''+CHAR(59)+'''
SELECT @MYSUBSTR9 = '''+CHAR(59)+'''
SELECT @MYSUBWHR2 = ''' + @MYWHR2 + '''
SELECT @MYSUBSTP = ''' + @MYSTP + '''
SELECT @MYSUBINT = 0
SELECT @MYSUBMAX = 17544'

-- Replace " | " with double apostrophes here.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBTBL = REPLACE(@MYSUBTBL,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBOUT = REPLACE(@MYSUBOUT,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBPIV = REPLACE(@MYSUBPIV,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBWHR2 = REPLACE(@MYSUBWHR2,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
SELECT @MYSUBALT = REPLACE(@MYSUBALT,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''') '

-- Initialise secondary execution code.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBSTR = ''CREATE VIEW '' + @MYSUBQRY + '' AS SELECT TOP 100 PERCENT '' + @MYSUBOUT'

-- Add secondary execution iteration to identify and create the pivoted columns.
SELECT @MYSTR = @MYSTR + '
DECLARE curXTAB CURSOR FAST_FORWARD FOR
SELECT DISTINCT '+@MYPIV+' 
FROM '+@MYTBL+' 
'+@MYWHR1+' 
ORDER BY '+@MYPIV+'

OPEN curXTAB
FETCH NEXT FROM curXTAB
INTO @MYXCOL
WHILE @@FETCH_STATUS = 0
BEGIN

If @MYSUBINT <=  @MYSUBMAX
BEGIN
SELECT @MYSUBINT = @MYSUBINT + 1

SELECT @MYXCOL = IsNull(@MYXCOL,''NULL'')'

-- Build a series of secondary execution strings accomodating up to 63000 characters of dynamic sql.
SELECT @MYSTR = @MYSTR + '
If (Len(@MYSUBSTR) < 7001)
BEGIN
SELECT @MYSUBSTR = @MYSUBSTR  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR1) < 7001)
BEGIN
SELECT @MYSUBSTR1 = @MYSUBSTR1  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR2) < 7001)
BEGIN
SELECT @MYSUBSTR2 = @MYSUBSTR2  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR3) < 7001)
BEGIN
SELECT @MYSUBSTR3 = @MYSUBSTR3  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR4) < 7001)
BEGIN
SELECT @MYSUBSTR4 = @MYSUBSTR4  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR5) < 7001)
BEGIN
SELECT @MYSUBSTR5 = @MYSUBSTR5  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR6) < 7001)
BEGIN
SELECT @MYSUBSTR6 = @MYSUBSTR6  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR7) < 7001)
BEGIN
SELECT @MYSUBSTR7 = @MYSUBSTR7  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR8) < 7001)
BEGIN
SELECT @MYSUBSTR8 = @MYSUBSTR8  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR9) < 7001)
BEGIN
SELECT @MYSUBSTR9 = @MYSUBSTR9  + '',
''+@MYSUBSUM+''(CASE '' + @MYSUBPIV + '' WHEN ''+CHAR(39)+@MYXCOL+CHAR(39)+'' THEN ''+@MYSUBFLD+'' ELSE ''+ @MYSUBALT+'' END) AS '' + IsNull(''_'' + REPLACE(@MYXCOL,'' '',''''),''_NULL'')
END
ELSE
If (Len(@MYSUBSTR9) >= 7000)
BEGIN
SELECT @MYSUBSTR9 = ''SELECT XTAB_ERROR__QUERY_TOO_LARGE_FOR_THIS_VERSION_OF_XTAB''
END

END
ELSE'
-- Clear all dynamic strings if more than 17544 distinct rows to pivot.
SELECT @MYSTR = @MYSTR + '
BEGIN
SELECT @MYSUBSTR1 = ''''
SELECT @MYSUBSTR2 = ''''
SELECT @MYSUBSTR3 = ''''
SELECT @MYSUBSTR4 = ''''
SELECT @MYSUBSTR5 = ''''
SELECT @MYSUBSTR6 = ''''
SELECT @MYSUBSTR7 = ''''
SELECT @MYSUBSTR8 = ''''
SELECT @MYSUBSTR9 = ''''
SELECT @MYSUBWHR2 = ''''
SELECT @MYSUBSTP = ''EXEC(| XTAB_ERROR__MORE_THAN_17544_DISTINCT_ROWS_TO_PIVOT|''
END

FETCH NEXT FROM curXTAB
INTO @MYXCOL
END

CLOSE curXTAB
DEALLOCATE curXTAB'

-- Initialise secondary FROM WHERE GROUP and ORDER BY statements.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBEND = '' 
FROM '' + @MYSUBTBL

If Len(@MYSUBWHR2) > 0 
BEGIN 
SELECT @MYSUBEND =  @MYSUBEND + '' 
'' + @MYSUBWHR2 + '' ''
END

If Len(@MYSUBSUM) > 0 
BEGIN 
SELECT @MYSUBEND = @MYSUBEND + ''
GROUP BY '' + @MYSUBOUT
END

SELECT @MYSUBEND = @MYSUBEND + ''
ORDER BY '' + @MYSUBOUT'

-- Remove the CHAR(59) padding from second execution strings.
SELECT @MYSTR = @MYSTR + '
SELECT @MYSUBSTR1 = SUBSTRING(@MYSUBSTR1,2,8000)
SELECT @MYSUBSTR2 = SUBSTRING(@MYSUBSTR2,2,8000)
SELECT @MYSUBSTR3 = SUBSTRING(@MYSUBSTR3,2,8000)
SELECT @MYSUBSTR4 = SUBSTRING(@MYSUBSTR4,2,8000)
SELECT @MYSUBSTR5 = SUBSTRING(@MYSUBSTR5,2,8000)
SELECT @MYSUBSTR6 = SUBSTRING(@MYSUBSTR6,2,8000)
SELECT @MYSUBSTR7 = SUBSTRING(@MYSUBSTR7,2,8000)
SELECT @MYSUBSTR8 = SUBSTRING(@MYSUBSTR8,2,8000)
SELECT @MYSUBSTR9 = SUBSTRING(@MYSUBSTR9,2,8000)'

-- Secondary execution code with zero to 63000+ character tolerance.
SELECT @MYSTR = @MYSTR + '
If Len(@MYSUBSTR1) < 1
BEGIN 
EXEC(@MYSUBSTR + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR2) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR3) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR4) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR5) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR6) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR7) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 + 

@MYSUBEND)
END
ELSE
If Len(@MYSUBSTR8) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 + 

@MYSUBSTR7 + @MYSUBEND)
END
ELSE
If Len(@MYSUBSTR9) < 1
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 + 

@MYSUBSTR7 + @MYSUBSTR8 + @MYSUBEND)
END
ELSE
BEGIN
EXEC(@MYSUBSTR + @MYSUBSTR1 + @MYSUBSTR2 + @MYSUBSTR3 + @MYSUBSTR4 + @MYSUBSTR5 + @MYSUBSTR6 + 

@MYSUBSTR7 + @MYSUBSTR8 + @MYSUBSTR9 + @MYSUBEND)
END'

-- Initialise secondary (optional) output statement.
SELECT @MYSTR = @MYSTR + '
If Len(@MYSUBSTP) > 2 
BEGIN 
SELECT @MYSUBSTP = REPLACE(@MYSUBSTP,'''+CHAR(124)+''','''+CHAR(39)+CHAR(39)+''')
EXEC(@MYSUBSTP) 
END'

EXEC(@MYSTR)
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating