XTAB - CROSSTAB UTILITY

-- 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