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 I (Code Generator)
T-SQL Hints and Tips
Crosstab Query - PART I (Code Generator)
by
donutman
Posted
(Edited
)
ItÆs unfortunate that MSSQL Server does not have a direct method to create a pivot table or a crosstab query as does Access. It is likely that Yukon will introduce it, but until then you can generate the code necessary to perform a crosstab query using the Stored Procedure below. The output of the SP is a simple PRINT statement that will give the user the ability to cut and paste the resulting code into QA for execution and/or saving it as a View or SP of its own. A second version of the SP is intended to be used with an application front-end. It is developed as a separate FAQ, because there is additional logic to guard against SQL injection attacks.
This SP is very handy in that it relieves you of the tedium associated with typing a very long crosstab query. It's especially helpful when the number of or value of the rows in the pivot column is unknown until you run the query.
The spCrossTabPrint SP does the following:
[ul]
[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,[/li]
[li]creates a separate column named ôNULLSö when the pivot column includes a NULL and[/li]
[li]formats the output query so that it is more easily understood.[/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.
The following Stored Procedure looks more intimidating than it is. The VAST majority of the code is for error checking. The actual code-generating algorithm only requires about one page of code!
************************************
STORED PROCEDURE CODE FOLLOWS:
************************************
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spCrossTabPrint
@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]
--Delete white space
[/green] [Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray][red]' '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[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]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] @ChrPos[Gray]=[/Gray]0
[Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[green]
---Check for valid parameters in this section---
[/green] [green]---Look in the right place for the output---
[/green] [Blue]SELECT[/Blue] [red]'Check the Message Tab for your output.'[/red]
[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]PRINT[/Blue] [red]'There is an error in Parameter #1'[/red]
[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]PRINT[/Blue] [red]'You did not enter a SELECT clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue] [green]--May as well capatilize the keywords for looks.
[/green] [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]6[Gray],[/Gray][red]'SELECT'[/red][Gray])[/Gray]
[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]PRINT[/Blue] [red]'There is an error in Parameter #2.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]PRINT[/Blue] [red]'You did not enter a FROM clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]5[Gray],[/Gray][red]' FROM'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]PRINT[/Blue] [red]'You did not enter a GROUP BY clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' GROUP BY '[/red][Gray])[/Gray]
[Blue]END[/Blue]
[green]
-- Just to make the code look good.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' where '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]7[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' WHERE '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' having '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]8[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' HAVING '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' order by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' ORDER BY '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'='[/red][Gray],[/Gray]@PivotColumn[Gray])[/Gray][Gray]>[/Gray]0
[Blue]PRINT[/Blue] [red]'Warning: do not use an alias or a Named Column in parameter #3 (@PivotColumn). If you did, the SP will crash.'[/red][green]
------ Finished validity checking -------
[/green][green]
----This section devoted to @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] @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 [Gray],[/Gray]
@AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]Agg[Gray])[/Gray][Gray],[/Gray]Agg[Gray])[/Gray] [green]--May as well format it correctly
[/green] [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]PRINT[/Blue] [red]'You cannot have more than one aggregate function in Parameter #2.'[/red]
[Blue]IF[/Blue] @RgtPos[Gray]=[/Gray]0
[Blue]PRINT[/Blue] [red]'You did not enter an aggregate function in Parameter #2.'[/red][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 parenthesis 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]PRINT[/Blue] [red]'Your parenthesis are not balanced in Parameter #2.'[/red]
[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]' 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]
[Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue] [green]
--Listed here and below so that you can remove
[/green][green]--the sysobjects insert from below (if you like)
[/green][green]--but still keep the most dangerous ones in #WMD.
[/green] [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 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]PRINT[/Blue] [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]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Use @SqlPivot to modify @QueryTemplate by making the first column the pivot column, run the query and insert
[/green] [green]-- the PivotColumn values into a global temporary table. 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]
[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]
[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
[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 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]PRINT[/Blue] [red]'There is another global temporary table named ##CrossTab. Cannot continue.'[/red]
[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]PRINT[/Blue] [red]'There is an error in Parameter #3.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Create a template for each CASE clause that 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 surround the comparison values in the CASE clause.
[/green] [green]-- Varchar, Char and Date data types require the 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]Char[/Fuchsia][Gray]([/Gray]13[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 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] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray] [Gray]+[/Gray] [red]' '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]8000
[Blue]PRINT[/Blue] [red]'WARNING: The length of the query string is too long!'[/red]
[Blue]PRINT[/Blue] @QueryTemplate
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]ON[/Blue]
[Blue]GO[/Blue]
************************************
This example using the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.
This example illustrates that complex parameters (including a computed pivot column) are handled properly.
This example from the Northwinds database illustrates that even pivot columns that contain embedded single quotes are also handled properly.
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
This SP is very handy in that it relieves you of the tedium associated with typing a very long crosstab query. It's especially helpful when the number of or value of the rows in the pivot column is unknown until you run the query.
The spCrossTabPrint SP does the following:
[ul]
[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,[/li]
[li]creates a separate column named ôNULLSö when the pivot column includes a NULL and[/li]
[li]formats the output query so that it is more easily understood.[/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.
The following Stored Procedure looks more intimidating than it is. The VAST majority of the code is for error checking. The actual code-generating algorithm only requires about one page of code!
************************************
STORED PROCEDURE CODE FOLLOWS:
************************************
[Blue]CREATE[/Blue] [Blue]PROCEDURE[/Blue] spCrossTabPrint
@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]
--Delete white space
[/green] [Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @AggregateFunction[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]LTrim[/Fuchsia][Gray]([/Gray][Fuchsia]Rtrim[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]SET[/Blue] @QueryTemplate[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray][red]' '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray]0
[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]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] @ChrPos[Gray]=[/Gray]0
[Blue]WHILE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray][Gray]<[/Gray][Gray]>[/Gray]@ChrPos [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @ChrPos[Gray]=[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' '[/red][Gray],[/Gray][red]' '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' ('[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]'( '[/red][Gray],[/Gray][red]'('[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]') '[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]SET[/Blue] @PivotColumn[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]@PivotColumn[Gray],[/Gray][red]' )'[/red][Gray],[/Gray][red]')'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[green]
---Check for valid parameters in this section---
[/green] [green]---Look in the right place for the output---
[/green] [Blue]SELECT[/Blue] [red]'Check the Message Tab for your output.'[/red]
[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]PRINT[/Blue] [red]'There is an error in Parameter #1'[/red]
[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]PRINT[/Blue] [red]'You did not enter a SELECT clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue] [green]--May as well capatilize the keywords for looks.
[/green] [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]6[Gray],[/Gray][red]'SELECT'[/red][Gray])[/Gray]
[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]PRINT[/Blue] [red]'There is an error in Parameter #2.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' from '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]PRINT[/Blue] [red]'You did not enter a FROM clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]5[Gray],[/Gray][red]' FROM'[/red][Gray])[/Gray]
[Blue]END[/Blue]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' group by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]=[/Gray]0 [Blue]BEGIN[/Blue]
[Blue]PRINT[/Blue] [red]'You did not enter a GROUP BY clause in the 1st parameter.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[Blue]ELSE[/Blue] [Blue]BEGIN[/Blue]
[Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' GROUP BY '[/red][Gray])[/Gray]
[Blue]END[/Blue]
[green]
-- Just to make the code look good.
[/green] [Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' where '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]7[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' WHERE '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' having '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]8[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' HAVING '[/red][Gray])[/Gray]
[Blue]SET[/Blue] @ChrPos [Gray]=[/Gray] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]' order by '[/red][Gray],[/Gray]@QueryTemplate[Gray])[/Gray]
[Blue]IF[/Blue] @ChrPos[Gray]>[/Gray]0 [Blue]SET[/Blue] @QueryTemplate [Gray]=[/Gray] [Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray],[/Gray]@ChrPos[Gray],[/Gray]10[Gray],[/Gray][Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray][Gray]+[/Gray][red]' ORDER BY '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]CharIndex[/Fuchsia][Gray]([/Gray][red]'='[/red][Gray],[/Gray]@PivotColumn[Gray])[/Gray][Gray]>[/Gray]0
[Blue]PRINT[/Blue] [red]'Warning: do not use an alias or a Named Column in parameter #3 (@PivotColumn). If you did, the SP will crash.'[/red][green]
------ Finished validity checking -------
[/green][green]
----This section devoted to @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] @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 [Gray],[/Gray]
@AggregateFunction[Gray]=[/Gray][Fuchsia]Stuff[/Fuchsia][Gray]([/Gray]@AggregateFunction[Gray],[/Gray]@RgtPos[Gray],[/Gray][Fuchsia]Len[/Fuchsia][Gray]([/Gray]Agg[Gray])[/Gray][Gray],[/Gray]Agg[Gray])[/Gray] [green]--May as well format it correctly
[/green] [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]PRINT[/Blue] [red]'You cannot have more than one aggregate function in Parameter #2.'[/red]
[Blue]IF[/Blue] @RgtPos[Gray]=[/Gray]0
[Blue]PRINT[/Blue] [red]'You did not enter an aggregate function in Parameter #2.'[/red][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 parenthesis 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]PRINT[/Blue] [red]'Your parenthesis are not balanced in Parameter #2.'[/red]
[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]' 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]
[Blue]SELECT[/Blue] [red]' sp_sqlexec '[/red] [Blue]UNION[/Blue] [green]
--Listed here and below so that you can remove
[/green][green]--the sysobjects insert from below (if you like)
[/green][green]--but still keep the most dangerous ones in #WMD.
[/green] [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 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]PRINT[/Blue] [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]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Use @SqlPivot to modify @QueryTemplate by making the first column the pivot column, run the query and insert
[/green] [green]-- the PivotColumn values into a global temporary table. 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]
[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]
[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
[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 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]PRINT[/Blue] [red]'There is another global temporary table named ##CrossTab. Cannot continue.'[/red]
[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]PRINT[/Blue] [red]'There is an error in Parameter #3.'[/red]
[Blue]RETURN[/Blue]
[Blue]END[/Blue]
[green]
-- Create a template for each CASE clause that 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 surround the comparison values in the CASE clause.
[/green] [green]-- Varchar, Char and Date data types require the 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]Char[/Fuchsia][Gray]([/Gray]13[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 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] [Fuchsia]Char[/Fuchsia][Gray]([/Gray]13[Gray])[/Gray] [Gray]+[/Gray] [red]' '[/red][Gray])[/Gray]
[Blue]IF[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]@QueryTemplate[Gray])[/Gray][Gray]=[/Gray]8000
[Blue]PRINT[/Blue] [red]'WARNING: The length of the query string is too long!'[/red]
[Blue]PRINT[/Blue] @QueryTemplate
[Blue]SET[/Blue] [Blue]Ansi_Warnings[/Blue] [Blue]ON[/Blue]
[Blue]GO[/Blue]
************************************
This example using the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.
Code:
[Blue]EXEC[/Blue] spCrossTabPrint
[red]'select title from pubs.dbo.titles t inner join pubs.dbo.sales s on (s.title_id=t.title_id) group by title'[/red][Gray],[/Gray]
[red]'IsNull(sum(qty),0)'[/red][Gray],[/Gray]
[red]'stor_id'[/red]Results of Example I
Code:
SELECT title,
[6380] = IsNull(Sum(CASE stor_id WHEN '6380' THEN qty END),0),
[7066] = IsNull(Sum(CASE stor_id WHEN '7066' THEN qty END),0),
[7067] = IsNull(Sum(CASE stor_id WHEN '7067' THEN qty END),0),
[7131] = IsNull(Sum(CASE stor_id WHEN '7131' THEN qty END),0),
[7896] = IsNull(Sum(CASE stor_id WHEN '7896' THEN qty END),0),
[8042] = IsNull(Sum(CASE stor_id WHEN '8042' THEN qty END),0)
FROM pubs.dbo.titles t inner join pubs.dbo.sales s on (s.title_id=t.title_id)
GROUP BY titleThis example illustrates that complex parameters (including a computed pivot column) are handled properly.
Code:
[Blue]EXEC[/Blue] spCrossTabPrint
[red]'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) group by pub_name'[/red][Gray],[/Gray]
[red]'avg(case when price<5 then 5 else price end)'[/red][Gray],[/Gray]
[red]'round(price*qty/100,0)*100'[/red]Results of Example II
Code:
SELECT pub_name as Publisher, count(qty) as Orders, sum(qty) as Qty,
[0.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 0.00 THEN case when price<5 then 5 else price end END),
[100.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 100.00 THEN case when price<5 then 5 else price end END),
[200.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 200.00 THEN case when price<5 then 5 else price end END),
[300.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 300.00 THEN case when price<5 then 5 else price end END),
[400.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 400.00 THEN case when price<5 then 5 else price end END),
[700.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 700.00 THEN case when price<5 then 5 else price end END),
[800.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 800.00 THEN case when price<5 then 5 else price end END),
[1000.00] = Avg(CASE round(price*qty/100,0)*100 WHEN 1000.00 THEN case when price<5 then 5 else price end END)
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)
GROUP BY pub_name
Code:
[Blue]DECLARE[/Blue] @C [Blue]varchar[/Blue][Gray]([/Gray]200[Gray])[/Gray]
[Blue]SET[/Blue] @C[Gray]=[/Gray][red]'C'
[/red][Blue]SET[/Blue] @C[Gray]=[/Gray] [red]'SELect LastName FROM Northwind.dbo.Employees e INNER JOIN Northwind.dbo.Orders o ON (E.EmployeeID=O.EmployeeID) where ShipName<'[/red][Gray]+[/Gray]@C[Gray]+[/Gray][red]' GROUP BY LastName'[/red]
[Blue]EXEC[/Blue] spCrossTabPrint
@C[Gray],[/Gray]
[red]'count(lastname)'[/red][Gray],[/Gray]
[red]'Left(ShipName,8)'[/red]Results of Example III
Code:
SELECT LastName,
[Alfreds ] = Count(CASE Left(ShipName,8) WHEN 'Alfreds ' THEN lastname END),
[Alfred's] = Count(CASE Left(ShipName,8) WHEN 'Alfred's' THEN lastname END),
[Ana Truj] = Count(CASE Left(ShipName,8) WHEN 'Ana Truj' THEN lastname END),
[Antonio ] = Count(CASE Left(ShipName,8) WHEN 'Antonio ' THEN lastname END),
[Around t] = Count(CASE Left(ShipName,8) WHEN 'Around t' THEN lastname END),
[Berglund] = Count(CASE Left(ShipName,8) WHEN 'Berglund' THEN lastname END),
[Blauer S] = Count(CASE Left(ShipName,8) WHEN 'Blauer S' THEN lastname END),
[Blondel ] = Count(CASE Left(ShipName,8) WHEN 'Blondel ' THEN lastname END),
[B=lido C] = Count(CASE Left(ShipName,8) WHEN 'B=lido C' THEN lastname END),
[Bon app'] = Count(CASE Left(ShipName,8) WHEN 'Bon app' THEN lastname END),
[Bottom-D] = Count(CASE Left(ShipName,8) WHEN 'Bottom-D' THEN lastname END),
[B's Beve] = Count(CASE Left(ShipName,8) WHEN 'B's Beve' THEN lastname END)
FROM Northwind.dbo.Employees e INNER JOIN Northwind.dbo.Orders o ON (E.EmployeeID=O.EmployeeID)
WHERE ShipName<'C'
GROUP BY LastName-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.