Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Crosstab Query - PART II (Dynamic Execution)
T-SQL Hints and Tips
Crosstab Query - PART II (Dynamic Execution)
by
donutman
Posted
(Edited
)
In this version of the Crosstab query generator, the stored procedure, spCrossTabRun, will execute the dynamically generated query. Because this SP was designed to be exposed to "power" users who would then be able to dynamically execute SQL, code was added to check for WMD in the form of SQL injection attacks. All of the extra formatting of the crosstab query has been removed from this version of the SP as it is never seen.
The spCrossTabRun SP does the following:
[ul][li]guards against SQL injection attacks,[/li]
[li]does extensive error checking of the input parameters,[/li]
[li]uses RaisError to pass error messages back to the calling program,[/li]
[li]allows functions to be placed outside and inside the aggregate function,[/li]
[li]allows the pivot column to be a computed column,[/li]
[li]allows the pivot column to include data with an embedded single quote and[/li]
[li]creates a separate column named ôNULLSö when the pivot column includes a NULL.[/li]
[/ul]
There are only three string parameters that must be entered in order for the SP to generate the code. The parameters are explained in the order in which they should be entered.
@QueryTemplate
This is a working (but stripped down) SELECT query on the object table(s). It needs to include a GROUP BY clause for the summary column to be shown in each row. Don't include the pivot column nor the aggregate column to be used for the pivot values. Usually this means the only item in the Select list is the column(s) in the Group By clause. The FROM clause can contain joins and derived tables. IÆm sure there are limits beyond which the SP will not function, but you should find it quite flexible.
@AggregrateFunction
This as you can guess is the aggregate function including the column on which it is to be performed, e.g. Sum(SalesAmount). The column can also be a computed column, but do NOT name it nor ADD an alias within this parameter.
@PivotColumn
This is the column that contains the data that is to become the columns of the pivot table or crosstab query, e.g. Year(SalesDate). Note that you can indeed apply a function to the actual column. The column can also be a computed column, but again do NOT name it nor ADD an alias within this parameter.
***************************************
CODE FOR STORED PROCEDURE FOLLOWS:
***************************************
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spCrossTabRun
@QueryTemplate [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@AggregateFunction [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray][Gray],[/Gray]
@PivotColumn [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray]
[Blue]AS[/Blue]
[Blue]SET[/Blue] [Blue]NoCount[/Blue] [Blue]ON[/Blue]
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]Off[/Blue]
[Blue]DECLARE[/Blue] @SqlCase [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@SqlPivot [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] @Delimiter [Blue]varchar[/Blue][Gray]([/Gray]1[Gray])[/Gray]
[Blue]DECLARE[/Blue] @ChrPos [Blue]int[/Blue] [green]--Character position
[/green] [Blue]DECLARE[/Blue] @Aggs [Blue]varchar[/Blue][Gray]([/Gray]64[Gray])[/Gray][Gray],[/Gray] @BeforeAgg [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@AfterAgg [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @LftPos [Blue]int[/Blue][Gray],[/Gray] @RgtPos [Blue]int[/Blue]
[green]
-- Check for valid parameters in this section --
[/green] [Blue]EXEC[/Blue][Gray]([/Gray][red]'SET PARSEOnly ON; '[/red] [Gray]+[/Gray] @QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #1'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a SELECT clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][red]'SET PARSEOnly ON; '[/red] [Gray]+[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@AggregateFunction[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
[Blue]EXEC[/Blue] [Gray]([/Gray]@BeforeAgg[Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a FROM clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a GROUP BY clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
--This section is devoted to the @AggregateFunction parameter only --
[/green] [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #AggFunctions [Gray]([/Gray]Agg [Blue]varchar[/Blue][Gray]([/Gray]11[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @Aggs[Gray]=[/Gray][red]'AVG(,COUNT(,COUNT_BIG(,MAX(,MIN(,STDEV(,STDEVP(,SUM(,VAR(,VARP(,'[/red][green]
--Build a temporary table of all the agg functions
[/green] [Blue]WHILE[/Blue] @Aggs[Gray]<[/Gray][Gray]>[/Gray][red]'[/red] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]','[/red][Gray],[/Gray]@Aggs[Gray])[/Gray]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #AggFunctions
[Blue]SELECT[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]-[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @Aggs[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray],[/Gray]64[Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray])[/Gray][green]
--Get rid of unnecessary blanks
[/green] [Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0 [green]--Find the location of the aggregate function
[/green] [Blue]SELECT[/Blue] @RgtPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray]@LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]@RgtPos
[Blue]FROM[/Blue] #AggFunctions [Blue]WHERE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray]>[/Gray]0
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #AggFunctions
[Blue]IF[/Blue] @RgtPos[Gray]<[/Gray][Gray]>[/Gray]@LftPos
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You cannot have more than one aggregate function in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]IF[/Blue] @RgtPos[Gray]=[/Gray]0
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter an aggregate function in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray][green]
--Get any non-agg functions that occur prior to the agg function
[/green] [Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray]-[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray]8000[Gray])[/Gray]
[Blue]WHILE[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Gray]AND[/Gray] [Fuchsia]Right[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0[green]
--Count the number of parentheses before the agg function
[/green] [Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]+[/Gray]1
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][green]
--Count back from the right the same number of parentheses from above
[/green] [Blue]WHILE[/Blue] @LftPos[Gray]>[/Gray][Gray]-[/Gray]1 [Blue]BEGIN[/Blue]
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]-[/Gray]1
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BREAK[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'Your parenthesis are not balanced in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue][green]
--Get the tail end of non-agg functions
[/green] [Blue]SET[/Blue] @AfterAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray][green]
--This is the agg function with any INTERIOR functions
[/green] [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray] [green]
-- Finished with @AggregateFunction --
[/green][green]
--Hunt for SQL injections that use WMD.
[/green] [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #WMD [Gray]([/Gray]Weapon [Blue]varchar[/Blue][Gray]([/Gray]128[Gray])[/Gray][Gray])[/Gray]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
[Blue]SELECT[/Blue] [red]' alter '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' bcp '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' delete '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' drop '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' exec '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' exec('[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' execute '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' execute('[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' insert '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' openquery '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' restore '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' shutdown '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' truncate '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' update '[/red] [Blue]UNION[/Blue][green]
--The sp_ and xp_ are listed here and again in the sysobjects
[/green] [green]--insert from below, so that if you like,
[/green] [green]--you can remove the sysobjects insert,
[/green] [green]--but still keep the most dangerous ones in #WMD.
[/green] [Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' sp_executesql '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' xp_cmdshell '[/red]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
[Blue]SELECT[/Blue] [red]';'[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray] [Blue]FROM[/Blue] #WMD [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [Name] [Blue]FROM[/Blue] Master.dbo.sysobjects
[Blue]WHERE[/Blue] xType[Gray]=[/Gray][red]'P'[/red] [Gray]OR[/Gray] xType[Gray]=[/Gray][red]'X'[/red]
[green]
-- Strip out literals from the parameters
[/green] [green]-- so they are not identified as keywords.
[/green] [Blue]DECLARE[/Blue] @AllParameters [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @ParseParam [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @Cnt [Blue]int[/Blue][Gray],[/Gray] @End [Blue]int[/Blue]
[Blue]SET[/Blue] @AllParameters[Gray]=[/Gray][red]' '[/red][Gray]+[/Gray]@QueryTemplate[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@AggregateFunction[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@PivotColumn[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray]
[Blue]SET[/Blue] @ParseParam[Gray]=[/Gray][red]'[/red]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]1
[Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AllParameters[Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]@Cnt[Gray]+[/Gray]1
[Blue]SET[/Blue] @End[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray][Gray],[/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray])[/Gray]
[Blue]IF[/Blue] @Cnt [Gray]%[/Gray] 2 [Gray]=[/Gray] 0 [Blue]SET[/Blue] @ParseParam[Gray]=[/Gray]@ParseParam[Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray],[/Gray]@End[Gray]-[/Gray]@ChrPos[Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@End[Gray]+[/Gray]1
[Blue]END[/Blue][green]
--Now check for WMD
[/green] [Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray][red]'[/red]
[Blue]SELECT[/Blue] @SqlPivot[Gray]=[/Gray]@SqlPivot[Gray]+[/Gray]
[Blue]CASE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Weapon[Gray],[/Gray]@ParseParam[Gray])[/Gray]
[Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]'[/red] [Blue]ELSE[/Blue] [red]', '[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray][Gray])[/Gray] [Blue]END[/Blue]
[Blue]FROM[/Blue] #WMD
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #WMD
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray])[/Gray][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray][red]'Your parameters contain a possible SQL injection attack:'[/red]
[Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]3[Gray],[/Gray]8000[Gray])[/Gray][Gray]+[/Gray][red]'.'[/red]
[Blue]RAISERROR[/Blue] [Gray]([/Gray]@SqlPivot[Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Use @SqlPivot to modify @QueryTemplate by making
[/green] [green]-- the first column the pivot column, run the query and insert
[/green] [green]-- the PivotColumn values into a global temporary table.
[/green] [green]-- The values will become the names of the cross tab columns.
[/green] [Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray]@QueryTemplate
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn [Gray]+[/Gray] [red]' as PivotColumn, '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray],[/Gray]0[Gray],[/Gray][red]' INTO ##CrossTab '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]WHILE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray][Gray]>[/Gray]0 [green]--Find the last GROUP BY clause.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
[green]
-- Check to see if someone else is using ##CrossTab
[/green] [green]-- at the same time and wait 10 seconds.
[/green] [Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray]
[Blue]WAITFOR[/Blue] [Blue]DELAY[/Blue] [red]'000:00:10'[/red]
[Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is another global temporary table named ##CrossTab. Cannot continue.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]EXEC[/Blue] [Gray]([/Gray]@SqlPivot[Gray])[/Gray]
[Blue]IF[/Blue] [Gray]NOT[/Gray] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #3.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Create a template for each CASE clause that
[/green] [green]-- will needed for the crosstab columns in the SELECT clause.
[/green] [green]-- The @AfterAgg gets added here, but the @BeforeAgg is inserted later.
[/green] [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray] 1[Gray],[/Gray] [red]' END)'[/red] [Gray]+[/Gray] @AfterAgg[Gray])[/Gray]
[green]
-- Determine whether or not to use a ' to
[/green] [green]-- surround the comparison values in the CASE clause.
[/green] [green]-- Varchar, Char and Date data types require the
[/green] [green]-- apostrophe whereas numbers do not require a delimiter.
[/green] [Blue]SELECT[/Blue] @Delimiter [Gray]=[/Gray] [Fuchsia]Left[/Fuchsia][Gray]([/Gray][red]''[/red][Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray])[/Gray]
[Blue]FROM[/Blue] ##CrossTab [Blue]WHERE[/Blue] [Fuchsia]IsNumeric[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]PivotColumn [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray])[/Gray][Gray])[/Gray][Gray]=[/Gray]0
[green]
-- Generate all of the CASE statements needed in the SELECT clause.
[/green] [Blue]SELECT[/Blue] @SqlCase[Gray]=[/Gray][Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray]@SqlCase[Gray],[/Gray][red]'[/red][Gray])[/Gray] [Gray]+[/Gray][red]', ['[/red] [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]'NULLS'[/red][Gray])[/Gray] [Gray]+[/Gray] [red]'] = '[/red] [Gray]+[/Gray]
@BeforeAgg[Gray]+[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray] [red]'('[/red][Gray],[/Gray] @AggregateFunction [Gray])[/Gray][Gray]+[/Gray]1[Gray],[/Gray] 0[Gray],[/Gray] [red]'CASE '[/red] [Gray]+[/Gray] @PivotColumn [Gray]+[/Gray] [red]' WHEN '[/red]
[Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]''[/red][Gray],[/Gray][red]'''[/red][Gray])[/Gray][Gray],[/Gray][red]'NULL'[/red][Gray])[/Gray] [Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [red]' THEN '[/red] [Gray])[/Gray]
[Blue]FROM[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Blue]TOP[/Blue] 100 [Blue]PERCENT[/Blue] PivotColumn [Blue]FROM[/Blue] ##CrossTab [Blue]ORDER[/Blue] [Blue]BY[/Blue] PivotColumn[Gray])[/Gray] DistinctPivots
[green]
---Fix syntax of NULLS to say IS NULL instead of WHEN NULL.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'[NULLS]'[/red][Gray],[/Gray]@SqlCase[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'(CASE '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray])[/Gray]
[Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]6[Gray],[/Gray]0[Gray],[/Gray][red]'WHEN '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' WHEN '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray])[/Gray]
[Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray] @ChrPos[Gray]+[/Gray]1[Gray],[/Gray]9[Gray]+[/Gray]2[Gray]*[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@Delimiter[Gray])[/Gray][Gray],[/Gray][red]'IS NULL'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] ##CrossTab
[green]
-- Insert the CASE statements into the SELECT clause
[/green] [green]-- that was provided by the user.
[/green] [Blue]SELECT[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' FROM '[/red][Gray],[/Gray] @QueryTemplate[Gray])[/Gray][Gray],[/Gray] 0[Gray],[/Gray] @SqlCase [Gray]+[/Gray] [red]' '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]8000
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'The length of the query string is too long.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]ELSE[/Blue]
[Blue]EXEC[/Blue] [Gray]([/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]ON[/Blue]
[Blue]GO[/Blue]
***************************************
Example 1 uses the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.
Results of Example I:
Example II illustrates that complex parameters (including a computed pivot column) are handled properly.
Results of example II
Example III uses the Northwinds database illustrates that even pivot columns that contain embedded single quotes are also handled properly.
Results of example III
Credit to robvolk at http://www.sqlteam.com/item.asp?ItemID=2955 and ESquared who insired me to push the envelope of his original work.
-Karl
The spCrossTabRun SP does the following:
[ul][li]guards against SQL injection attacks,[/li]
[li]does extensive error checking of the input parameters,[/li]
[li]uses RaisError to pass error messages back to the calling program,[/li]
[li]allows functions to be placed outside and inside the aggregate function,[/li]
[li]allows the pivot column to be a computed column,[/li]
[li]allows the pivot column to include data with an embedded single quote and[/li]
[li]creates a separate column named ôNULLSö when the pivot column includes a NULL.[/li]
[/ul]
There are only three string parameters that must be entered in order for the SP to generate the code. The parameters are explained in the order in which they should be entered.
@QueryTemplate
This is a working (but stripped down) SELECT query on the object table(s). It needs to include a GROUP BY clause for the summary column to be shown in each row. Don't include the pivot column nor the aggregate column to be used for the pivot values. Usually this means the only item in the Select list is the column(s) in the Group By clause. The FROM clause can contain joins and derived tables. IÆm sure there are limits beyond which the SP will not function, but you should find it quite flexible.
@AggregrateFunction
This as you can guess is the aggregate function including the column on which it is to be performed, e.g. Sum(SalesAmount). The column can also be a computed column, but do NOT name it nor ADD an alias within this parameter.
@PivotColumn
This is the column that contains the data that is to become the columns of the pivot table or crosstab query, e.g. Year(SalesDate). Note that you can indeed apply a function to the actual column. The column can also be a computed column, but again do NOT name it nor ADD an alias within this parameter.
***************************************
CODE FOR STORED PROCEDURE FOLLOWS:
***************************************
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spCrossTabRun
@QueryTemplate [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@AggregateFunction [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray][Gray],[/Gray]
@PivotColumn [Blue]varchar[/Blue][Gray]([/Gray]100[Gray])[/Gray]
[Blue]AS[/Blue]
[Blue]SET[/Blue] [Blue]NoCount[/Blue] [Blue]ON[/Blue]
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]Off[/Blue]
[Blue]DECLARE[/Blue] @SqlCase [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@SqlPivot [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] @Delimiter [Blue]varchar[/Blue][Gray]([/Gray]1[Gray])[/Gray]
[Blue]DECLARE[/Blue] @ChrPos [Blue]int[/Blue] [green]--Character position
[/green] [Blue]DECLARE[/Blue] @Aggs [Blue]varchar[/Blue][Gray]([/Gray]64[Gray])[/Gray][Gray],[/Gray] @BeforeAgg [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray]
@AfterAgg [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @LftPos [Blue]int[/Blue][Gray],[/Gray] @RgtPos [Blue]int[/Blue]
[green]
-- Check for valid parameters in this section --
[/green] [Blue]EXEC[/Blue][Gray]([/Gray][red]'SET PARSEOnly ON; '[/red] [Gray]+[/Gray] @QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #1'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a SELECT clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][red]'SET PARSEOnly ON; '[/red] [Gray]+[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@AggregateFunction[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
[Blue]EXEC[/Blue] [Gray]([/Gray]@BeforeAgg[Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]@@Error[/Fuchsia][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a FROM clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter a GROUP BY clause in the 1st parameter'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
--This section is devoted to the @AggregateFunction parameter only --
[/green] [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #AggFunctions [Gray]([/Gray]Agg [Blue]varchar[/Blue][Gray]([/Gray]11[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @Aggs[Gray]=[/Gray][red]'AVG(,COUNT(,COUNT_BIG(,MAX(,MIN(,STDEV(,STDEVP(,SUM(,VAR(,VARP(,'[/red][green]
--Build a temporary table of all the agg functions
[/green] [Blue]WHILE[/Blue] @Aggs[Gray]<[/Gray][Gray]>[/Gray][red]'[/red] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]','[/red][Gray],[/Gray]@Aggs[Gray])[/Gray]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #AggFunctions
[Blue]SELECT[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]-[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @Aggs[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@Aggs[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray],[/Gray]64[Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray])[/Gray][green]
--Get rid of unnecessary blanks
[/green] [Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0 [green]--Find the location of the aggregate function
[/green] [Blue]SELECT[/Blue] @RgtPos[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray]@LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]@RgtPos
[Blue]FROM[/Blue] #AggFunctions [Blue]WHERE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Agg[Gray],[/Gray]@AggregateFunction[Gray])[/Gray][Gray]>[/Gray]0
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #AggFunctions
[Blue]IF[/Blue] @RgtPos[Gray]<[/Gray][Gray]>[/Gray]@LftPos
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You cannot have more than one aggregate function in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]IF[/Blue] @RgtPos[Gray]=[/Gray]0
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'You did not enter an aggregate function in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray][green]
--Get any non-agg functions that occur prior to the agg function
[/green] [Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray]-[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray]8000[Gray])[/Gray]
[Blue]WHILE[/Blue] [Fuchsia]Left[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Gray]AND[/Gray] [Fuchsia]Right[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @BeforeAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]0[green]
--Count the number of parentheses before the agg function
[/green] [Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@BeforeAgg[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red]
[Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]+[/Gray]1
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][green]
--Count back from the right the same number of parentheses from above
[/green] [Blue]WHILE[/Blue] @LftPos[Gray]>[/Gray][Gray]-[/Gray]1 [Blue]BEGIN[/Blue]
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]'('[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]+[/Gray]1
[Blue]IF[/Blue] [Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray],[/Gray]1[Gray])[/Gray][Gray]=[/Gray][red]')'[/red] [Blue]SET[/Blue] @LftPos[Gray]=[/Gray]@LftPos[Gray]-[/Gray]1
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@ChrPos[Gray]-[/Gray]1
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BREAK[/Blue]
[Blue]END[/Blue]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'Your parenthesis are not balanced in Parameter #2.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue][green]
--Get the tail end of non-agg functions
[/green] [Blue]SET[/Blue] @AfterAgg[Gray]=[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]2[Gray],[/Gray]8000[Gray])[/Gray][green]
--This is the agg function with any INTERIOR functions
[/green] [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Left[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray] [green]
-- Finished with @AggregateFunction --
[/green][green]
--Hunt for SQL injections that use WMD.
[/green] [Blue]CREATE[/Blue] [Blue]TABLE[/Blue] #WMD [Gray]([/Gray]Weapon [Blue]varchar[/Blue][Gray]([/Gray]128[Gray])[/Gray][Gray])[/Gray]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
[Blue]SELECT[/Blue] [red]' alter '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' bcp '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' delete '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' drop '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' exec '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' exec('[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' execute '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' execute('[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' insert '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' openquery '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' restore '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' shutdown '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' truncate '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' update '[/red] [Blue]UNION[/Blue][green]
--The sp_ and xp_ are listed here and again in the sysobjects
[/green] [green]--insert from below, so that if you like,
[/green] [green]--you can remove the sysobjects insert,
[/green] [green]--but still keep the most dangerous ones in #WMD.
[/green] [Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' sp_executesql '[/red] [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [red]' xp_cmdshell '[/red]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] #WMD
[Blue]SELECT[/Blue] [red]';'[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray] [Blue]FROM[/Blue] #WMD [Blue]UNION[/Blue]
[Blue]SELECT[/Blue] [Name] [Blue]FROM[/Blue] Master.dbo.sysobjects
[Blue]WHERE[/Blue] xType[Gray]=[/Gray][red]'P'[/red] [Gray]OR[/Gray] xType[Gray]=[/Gray][red]'X'[/red]
[green]
-- Strip out literals from the parameters
[/green] [green]-- so they are not identified as keywords.
[/green] [Blue]DECLARE[/Blue] @AllParameters [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @ParseParam [Blue]varchar[/Blue] [Gray]([/Gray]8000[Gray])[/Gray]
[Blue]DECLARE[/Blue] @Cnt [Blue]int[/Blue][Gray],[/Gray] @End [Blue]int[/Blue]
[Blue]SET[/Blue] @AllParameters[Gray]=[/Gray][red]' '[/red][Gray]+[/Gray]@QueryTemplate[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@AggregateFunction[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray]@PivotColumn[Gray]+[/Gray][red]' '[/red][Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray]
[Blue]SET[/Blue] @ParseParam[Gray]=[/Gray][red]'[/red]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]1
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]1
[Blue]WHILE[/Blue] @ChrPos[Gray]<[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AllParameters[Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @Cnt[Gray]=[/Gray]@Cnt[Gray]+[/Gray]1
[Blue]SET[/Blue] @End[Gray]=[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]39[Gray])[/Gray][Gray],[/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray])[/Gray]
[Blue]IF[/Blue] @Cnt [Gray]%[/Gray] 2 [Gray]=[/Gray] 0 [Blue]SET[/Blue] @ParseParam[Gray]=[/Gray]@ParseParam[Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@AllParameters[Gray],[/Gray]@ChrPos[Gray],[/Gray]@End[Gray]-[/Gray]@ChrPos[Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]@End[Gray]+[/Gray]1
[Blue]END[/Blue][green]
--Now check for WMD
[/green] [Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray][red]'[/red]
[Blue]SELECT[/Blue] @SqlPivot[Gray]=[/Gray]@SqlPivot[Gray]+[/Gray]
[Blue]CASE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray]Weapon[Gray],[/Gray]@ParseParam[Gray])[/Gray]
[Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]'[/red] [Blue]ELSE[/Blue] [red]', '[/red][Gray]+[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]Weapon[Gray])[/Gray][Gray])[/Gray] [Blue]END[/Blue]
[Blue]FROM[/Blue] #WMD
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] #WMD
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray])[/Gray][Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray][red]'Your parameters contain a possible SQL injection attack:'[/red]
[Gray]+[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][Fuchsia]SubString[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]3[Gray],[/Gray]8000[Gray])[/Gray][Gray]+[/Gray][red]'.'[/red]
[Blue]RAISERROR[/Blue] [Gray]([/Gray]@SqlPivot[Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Use @SqlPivot to modify @QueryTemplate by making
[/green] [green]-- the first column the pivot column, run the query and insert
[/green] [green]-- the PivotColumn values into a global temporary table.
[/green] [green]-- The values will become the names of the cross tab columns.
[/green] [Blue]SET[/Blue] @SqlPivot[Gray]=[/Gray]@QueryTemplate
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'select '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]7[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn [Gray]+[/Gray] [red]' as PivotColumn, '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray],[/Gray]0[Gray],[/Gray][red]' INTO ##CrossTab '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray])[/Gray]
[Blue]WHILE[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray][Gray]>[/Gray]0 [green]--Find the last GROUP BY clause.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]1[Gray])[/Gray]
[Blue]SET[/Blue] @SqlPivot [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@SqlPivot[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray],[/Gray]0[Gray],[/Gray]@PivotColumn[Gray]+[/Gray][red]', '[/red][Gray])[/Gray]
[green]
-- Check to see if someone else is using ##CrossTab
[/green] [green]-- at the same time and wait 10 seconds.
[/green] [Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray]
[Blue]WAITFOR[/Blue] [Blue]DELAY[/Blue] [red]'000:00:10'[/red]
[Blue]IF[/Blue] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is another global temporary table named ##CrossTab. Cannot continue.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]EXEC[/Blue] [Gray]([/Gray]@SqlPivot[Gray])[/Gray]
[Blue]IF[/Blue] [Gray]NOT[/Gray] Exists [Gray]([/Gray][Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TempDB.dbo.SysObjects
[Blue]WHERE[/Blue] [ID] [Gray]=[/Gray] [Fuchsia]Object_ID[/Fuchsia][Gray]([/Gray]N[red]'TempDB.[dbo].[##CrossTab]'[/red][Gray])[/Gray] [Gray]AND[/Gray] xType[Gray]=[/Gray][red]'U'[/red][Gray])[/Gray] [Blue]BEGIN[/Blue]
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'There is an error in Parameter #3.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Create a template for each CASE clause that
[/green] [green]-- will needed for the crosstab columns in the SELECT clause.
[/green] [green]-- The @AfterAgg gets added here, but the @BeforeAgg is inserted later.
[/green] [Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray],[/Gray] 1[Gray],[/Gray] [red]' END)'[/red] [Gray]+[/Gray] @AfterAgg[Gray])[/Gray]
[green]
-- Determine whether or not to use a ' to
[/green] [green]-- surround the comparison values in the CASE clause.
[/green] [green]-- Varchar, Char and Date data types require the
[/green] [green]-- apostrophe whereas numbers do not require a delimiter.
[/green] [Blue]SELECT[/Blue] @Delimiter [Gray]=[/Gray] [Fuchsia]Left[/Fuchsia][Gray]([/Gray][red]''[/red][Gray],[/Gray] [Fuchsia]COUNT[/Fuchsia][Gray]([/Gray][Gray]*[/Gray][Gray])[/Gray][Gray])[/Gray]
[Blue]FROM[/Blue] ##CrossTab [Blue]WHERE[/Blue] [Fuchsia]IsNumeric[/Fuchsia][Gray]([/Gray][Fuchsia]Cast[/Fuchsia][Gray]([/Gray]PivotColumn [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray])[/Gray][Gray])[/Gray][Gray]=[/Gray]0
[green]
-- Generate all of the CASE statements needed in the SELECT clause.
[/green] [Blue]SELECT[/Blue] @SqlCase[Gray]=[/Gray][Fuchsia]Coalesce[/Fuchsia][Gray]([/Gray]@SqlCase[Gray],[/Gray][red]'[/red][Gray])[/Gray] [Gray]+[/Gray][red]', ['[/red] [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]'NULLS'[/red][Gray])[/Gray] [Gray]+[/Gray] [red]'] = '[/red] [Gray]+[/Gray]
@BeforeAgg[Gray]+[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray][Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray] [red]'('[/red][Gray],[/Gray] @AggregateFunction [Gray])[/Gray][Gray]+[/Gray]1[Gray],[/Gray] 0[Gray],[/Gray] [red]'CASE '[/red] [Gray]+[/Gray] @PivotColumn [Gray]+[/Gray] [red]' WHEN '[/red]
[Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [Fuchsia]IsNull[/Fuchsia][Gray]([/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray][Fuchsia]Convert[/Fuchsia][Gray]([/Gray][Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray][Gray],[/Gray] PivotColumn[Gray])[/Gray][Gray],[/Gray][red]''[/red][Gray],[/Gray][red]'''[/red][Gray])[/Gray][Gray],[/Gray][red]'NULL'[/red][Gray])[/Gray] [Gray]+[/Gray] @Delimiter [Gray]+[/Gray] [red]' THEN '[/red] [Gray])[/Gray]
[Blue]FROM[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] [Blue]TOP[/Blue] 100 [Blue]PERCENT[/Blue] PivotColumn [Blue]FROM[/Blue] ##CrossTab [Blue]ORDER[/Blue] [Blue]BY[/Blue] PivotColumn[Gray])[/Gray] DistinctPivots
[green]
---Fix syntax of NULLS to say IS NULL instead of WHEN NULL.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'[NULLS]'[/red][Gray],[/Gray]@SqlCase[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'(CASE '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray])[/Gray]
[Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]6[Gray],[/Gray]0[Gray],[/Gray][red]'WHEN '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' WHEN '[/red][Gray],[/Gray]@SqlCase[Gray],[/Gray]@ChrPos[Gray]+[/Gray]10[Gray])[/Gray]
[Blue]SET[/Blue] @SqlCase [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia] [Gray]([/Gray]@SqlCase[Gray],[/Gray] @ChrPos[Gray]+[/Gray]1[Gray],[/Gray]9[Gray]+[/Gray]2[Gray]*[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@Delimiter[Gray])[/Gray][Gray],[/Gray][red]'IS NULL'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]DROP[/Blue] [Blue]TABLE[/Blue] ##CrossTab
[green]
-- Insert the CASE statements into the SELECT clause
[/green] [green]-- that was provided by the user.
[/green] [Blue]SELECT[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' FROM '[/red][Gray],[/Gray] @QueryTemplate[Gray])[/Gray][Gray],[/Gray] 0[Gray],[/Gray] @SqlCase [Gray]+[/Gray] [red]' '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]8000
[Blue]RAISERROR[/Blue] [Gray]([/Gray][red]'The length of the query string is too long.'[/red][Gray],[/Gray]15[Gray],[/Gray][Gray]-[/Gray]1[Gray])[/Gray]
[Blue]ELSE[/Blue]
[Blue]EXEC[/Blue] [Gray]([/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]ON[/Blue]
[Blue]GO[/Blue]
***************************************
Example 1 uses the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.
Code:
EXEC spCrossTabRun
'select title from pubs.dbo.titles t inner join pubs.dbo.sales s on (s.title_id=t.title_id) group by title',
'IsNull(sum(qty),0)',
'stor_id'
Code:
[b][green]TITLE 6380 7066 7067 7131 7896 8042[/green][/b]
But Is It.... 0 0 0 0 0 30
Computer P... 0 0 0 20 0 0
Cooking wi... 0 0 0 0 0 25
Emotional.... 0 0 0 25 0 0
Fifty Year... 0 0 20 0 0 0
Is Anger t... 3 75 10 20 0 0
Life Witho... 0 0 0 25 0 0
Onions, Le... 0 0 40 0 0 0
Prolonged.... 0 0 0 15 0 0
Secrets of... 0 50 0 0 0 0
Silicon Va... 0 0 0 0 10 0
Straight T... 0 0 0 0 15 0
Sushi, Any... 0 0 20 0 0 0
The Busy E... 5 0 0 0 0 10
The Gourme... 0 0 0 25 0 15
You Can Co... 0 0 0 0 35 0
Code:
EXEC spCrossTabRun
'select pub_name as Publisher, count(qty) as Orders, sum(qty) as Qty from pubs.dbo.sales s inner join pubs.dbo.titles t on (s.title_id=t.title_id) inner join pubs.dbo.publishers p on (p.pub_id=t.pub_id) where round(price*qty/100,0)*100<500 group by pub_name',
'avg(case when price<5 then 5 else price end)',
'round(price*qty/100,0)*100'
Code:
[b][green]
Publisher Order Qty 0.00 100.00 200.00 300.00 400.00[/green][/b]
Algodata Infosystems 6 135 NULL 19.99 19.99 15.97 NULL
Binnet & Hardley 7 150 5 5 15.97 14.99 21.59
New Moon Books 8 208 10.95 7.975 8.6466 19.99 NULL
Code:
DECLARE @B varchar(200)
SET @B='B'
SET @B= 'SELect LastName FROM Northwind.dbo.Employees e INNER JOIN Northwind.dbo.Orders O ON (E.EmployeeID=O.EmployeeID) where ShipName<'+@B+' GROUP BY LastName'
EXEC spCrossTabRun
@B,
'count(lastname)',
'Left(ShipName,8)'
Code:
[b][green]
LastName Alfreds Alfred's Ana Truj Antonio Around [/green][/b]
Callahan 0 0 0 0 1
Davolio 0 2 0 1 3
Dodsworth 0 0 0 0 2
King 0 0 1 2 0
Leverling 0 1 2 3 2
Peacock 0 2 1 1 4
Suyama 1 0 0 0 1Credit to robvolk at http://www.sqlteam.com/item.asp?ItemID=2955 and ESquared who insired me to push the envelope of his original work.
-Karl
Please Note: 1 is Bad, 10 is Good :-)
Part and Inventory Search
-
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.