INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Crosstab Query - PART I (Code Generator) by donutman
Posted: 3 Jul 04 (Edited 12 Mar 05)

   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:
  • allows functions to be placed outside and inside the aggregate function,
  • allows the pivot column to be a computed column,
  • allows the pivot column to include data with an embedded single quote,
  • creates a separate column named ôNULLSö when the pivot column includes a NULL and
  • formats the output query so that it is more easily understood.
   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:
************************************
CREATE PROCEDURE spCrossTabPrint
   @QueryTemplate varchar(8000),
   @AggregateFunction varchar(100),
   @PivotColumn varchar(100)
AS
   SET NoCount ON
   SET Ansi_Warnings Off
   DECLARE @SqlCase varchar(8000),
           @SqlPivot varchar(8000), @Delimiter varchar(1)
   DECLARE @ChrPos int --Character position
   DECLARE @Aggs varchar(64), @BeforeAgg varchar(8000),
           @AfterAgg varchar (8000)
   DECLARE @LftPos int, @RgtPos int

   --Delete white space
   SET @QueryTemplate=LTrim(Rtrim(@QueryTemplate))
   SET @AggregateFunction=LTrim(Rtrim(@AggregateFunction))
   SET @PivotColumn=LTrim(Rtrim(@PivotColumn))
   SET @ChrPos=0
   WHILE Len(@QueryTemplate)<>@ChrPos BEGIN
      SET @ChrPos=Len(@QueryTemplate)
      SET @QueryTemplate=Replace(@QueryTemplate,'  ',' ')
   END
   SET @ChrPos=0
   WHILE Len(@AggregateFunction)<>@ChrPos BEGIN
      SET @ChrPos=Len(@AggregateFunction)
      SET @AggregateFunction=Replace(@AggregateFunction,'  ',' ')
      SET @AggregateFunction=Replace(@AggregateFunction,' (','(')
      SET @AggregateFunction=Replace(@AggregateFunction,'( ','(')
      SET @AggregateFunction=Replace(@AggregateFunction,') ',')')
      SET @AggregateFunction=Replace(@AggregateFunction,' )',')')
   END
   SET @ChrPos=0
   WHILE Len(@PivotColumn)<>@ChrPos BEGIN
      SET @ChrPos=Len(@PivotColumn)
      SET @PivotColumn=Replace(@PivotColumn,'  ',' ')
      SET @PivotColumn=Replace(@PivotColumn,' (','(')
      SET @PivotColumn=Replace(@PivotColumn,'( ','(')
      SET @PivotColumn=Replace(@PivotColumn,') ',')')
      SET @PivotColumn=Replace(@PivotColumn,' )',')')
   END

   ---Check for valid parameters in this section---
   ---Look in the right place for the output---
   SELECT 'Check the Message Tab for your output.'
   EXEC('SET ParseOnly ON ' + @QueryTemplate)
   IF @@Error>0 BEGIN
      PRINT 'There is an error in Parameter #1'
      RETURN
   END

   SET @ChrPos = CharIndex('select ',@QueryTemplate)
   IF @ChrPos=0 BEGIN
      PRINT 'You did not enter a SELECT clause in the 1st parameter.'
      RETURN
   END
   ELSE BEGIN --May as well capatilize the keywords for looks.
      SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,6,'SELECT')
   END

   SET @BeforeAgg='SET ParseOnly ON ' +
      Stuff(@QueryTemplate,@ChrPos+7,0,@AggregateFunction+', ')
   EXEC (@BeforeAgg)
   IF @@Error>0 BEGIN
      PRINT 'There is an error in Parameter #2.'
      RETURN
   END

   SET @ChrPos = CharIndex(' from ',@QueryTemplate)
   IF @ChrPos=0 BEGIN
      PRINT 'You did not enter a FROM clause in the 1st parameter.'
      RETURN
   END
   ELSE BEGIN
      SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,5,' FROM')
   END

   SET @ChrPos = CharIndex(' group by ',@QueryTemplate)
   IF @ChrPos=0 BEGIN
      PRINT 'You did not enter a GROUP BY clause in the 1st parameter.'
      RETURN
   END
   ELSE BEGIN
      SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,10,Char(13)+'   GROUP BY ')
   END

   -- Just to make the code look good.
   SET @ChrPos = CharIndex(' where ',@QueryTemplate)
   IF @ChrPos>0 SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,7,Char(13)+'   WHERE ')

   SET @ChrPos = CharIndex(' having ',@QueryTemplate)
   IF @ChrPos>0 SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,8,Char(13)+'   HAVING ')

   SET @ChrPos = CharIndex(' order by ',@QueryTemplate)
   IF @ChrPos>0 SET @QueryTemplate = Stuff(@QueryTemplate,@ChrPos,10,Char(13)+'   ORDER BY ')

   IF CharIndex('=',@PivotColumn)>0  
      PRINT 'Warning: do not use an alias or a Named Column in parameter #3 (@PivotColumn). If you did, the SP will crash.'
------  Finished validity checking  -------

----This section devoted to @AggregateFunction parameter only ----
   CREATE TABLE #AggFunctions (Agg varchar(11))
   SET @Aggs='Avg(,Count(,Count_Big(,Max(,Min(,StDev(,StDevP(,Sum(,Var(,VarP(,'
   --Build a temporary table of all the agg functions
   WHILE @Aggs<>' BEGIN
      SET @ChrPos=CharIndex(',',@Aggs)
      INSERT INTO #AggFunctions
         SELECT Left(@Aggs,@ChrPos-1)
      SET @Aggs=SubString(@Aggs,@ChrPos+1,64)
   END

   SET @LftPos=0  --Find the location of the aggregate function
   SELECT @RgtPos=CharIndex(Agg,@AggregateFunction),@LftPos=@LftPos+@RgtPos ,
                  @AggregateFunction=Stuff(@AggregateFunction,@RgtPos,Len(Agg),Agg) --May as well format it correctly
      FROM #AggFunctions WHERE CharIndex(Agg,@AggregateFunction)>0
   DROP TABLE #AggFunctions
   IF @RgtPos<>@LftPos
      PRINT 'You cannot have more than one aggregate function in Parameter #2.'
   IF @RgtPos=0
      PRINT 'You did not enter an aggregate function in Parameter #2.'
   --Get any non-agg functions that occur prior to the agg function
   SET @BeforeAgg=Left(@AggregateFunction,@RgtPos-1)
   SET @AggregateFunction=SubString(@AggregateFunction,@RgtPos,8000)
   WHILE Left(@BeforeAgg,1)='(' AND Right(@AggregateFunction,1)=')' BEGIN
      SET @BeforeAgg=SubString(@BeforeAgg,2,8000)
      SET @AggregateFunction=Left(@AggregateFunction,Len(@AggregateFunction)-1)
   END
   SET @ChrPos=1
   SET @LftPos=0
   --Count the number of parentheses before the agg function
   WHILE @ChrPos<=Len(@BeforeAgg) BEGIN
      IF SubString(@BeforeAgg,@ChrPos,1)='(' SET @LftPos=@LftPos+1
      IF SubString(@BeforeAgg,@ChrPos,1)=')' SET @LftPos=@LftPos-1
      SET @ChrPos=@ChrPos+1
   END
   SET @ChrPos=Len(@AggregateFunction)
   --Count back from the right the same number of parenthesis from above
   WHILE @LftPos>-1 BEGIN  
      IF SubString(@AggregateFunction,@ChrPos,1)='(' SET @LftPos=@LftPos+1
      IF SubString(@AggregateFunction,@ChrPos,1)=')' SET @LftPos=@LftPos-1
      SET @ChrPos=@ChrPos-1
      IF @ChrPos=0 BREAK
   END
   IF @ChrPos=0 BEGIN
      PRINT 'Your parenthesis are not balanced in Parameter #2.'
      RETURN
   END
   --Get the tail end of non-agg functions
   SET @AfterAgg=SubString(@AggregateFunction,@ChrPos+2,8000)  
   --This is the agg function with any INTERIOR functions
   SET @AggregateFunction=Left(@AggregateFunction,@ChrPos+1)  
----- Finished with @AggregateFunction -----


   --Hunt for SQL injections that use WMD.
   CREATE TABLE #WMD (Weapon varchar(128))
   INSERT INTO #WMD
      SELECT ' alter ' UNION
      SELECT ' bcp ' UNION
      SELECT ' delete ' UNION
      SELECT ' drop ' UNION
      SELECT ' exec ' UNION
      SELECT ' exec(' UNION
      SELECT ' execute ' UNION
      SELECT ' insert ' UNION
      SELECT ' openquery ' UNION
      SELECT ' restore ' UNION
      SELECT ' shutdown ' UNION
      SELECT ' truncate ' UNION
      SELECT ' update ' UNION
      SELECT ' sp_sqlexec ' UNION  
--Listed here and below so that you can remove
--the sysobjects insert from below (if you like)
--but still keep the most dangerous ones in #WMD.
      SELECT ' sp_executesql ' UNION  
      SELECT ' xp_cmdshell '  
   INSERT INTO #WMD
      SELECT ';'+LTrim(Weapon) FROM #WMD UNION
      SELECT [Name] FROM Master.dbo.sysobjects WHERE xType='P' OR xType='X'
   --Strip out literals from the parameters so they are not identified as keywords
   DECLARE @AllParameters varchar(8000)
   DECLARE @ParseParam varchar (8000)
   DECLARE @Cnt int, @End int
   SET @AllParameters=' '+@QueryTemplate+' '+@AggregateFunction+' '+@PivotColumn+' '+Char(39)
   SET @ParseParam='
   SET @ChrPos=1
   SET @Cnt=1
   WHILE @ChrPos<Len(@AllParameters) BEGIN
      SET @Cnt=@Cnt+1
      SET @End=CharIndex(Char(39),@AllParameters,@ChrPos)
      IF @Cnt % 2 = 0 SET @ParseParam=@ParseParam+SubString(@AllParameters,@ChrPos,@End-@ChrPos)
      SET @ChrPos=@End+1
   END
   --Now check for WMD
   SET @SqlPivot='                         
   SELECT @SqlPivot=@SqlPivot+
         CASE CharIndex(Weapon,@ParseParam)
            WHEN 0 THEN ' ELSE ', '+LTrim(Rtrim(Weapon)) END
      FROM #WMD

   DROP TABLE #WMD
   IF Len(@SqlPivot)>0 BEGIN
      PRINT 'Your parameters contain a possible SQL injection attack:'+Char(13)+SubString(@SqlPivot,3,8000)+'.'
      RETURN
   END

   -- Use @SqlPivot to modify @QueryTemplate by making the first column the pivot column, run the query and insert
   -- the PivotColumn values into a global temporary table.  The values will become the names of the cross tab columns.
   SET @SqlPivot = @QueryTemplate
   SET @ChrPos = CharIndex('select ',@SqlPivot)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos+7,0,@PivotColumn + ' as PivotColumn, ')
   SET @ChrPos = CharIndex(' from ',@SqlPivot)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos,0,' INTO ##CrossTab ')

--Find the last GROUP BY clause.
   SET @ChrPos = CharIndex(' group by ',@SqlPivot)
   WHILE CharIndex(' group by ',@SqlPivot,@ChrPos+1)>0
      SET @ChrPos = CharIndex(' group by ',@SqlPivot,@ChrPos+1)
   SET @SqlPivot = Stuff(@SqlPivot,@ChrPos+10,0,@PivotColumn+', ')

   -- Check to see if someone else is using ##CrossTab at the same time and wait 10 seconds.
   IF Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U')
      WAITFOR DELAY '000:00:10'
   IF Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U') BEGIN
      PRINT 'There is another global temporary table named ##CrossTab.  Cannot continue.'
       RETURN
   END

   EXEC (@SqlPivot)
   IF NOT Exists (SELECT * FROM TempDB.dbo.SysObjects
         WHERE [ID] = Object_ID(N'TempDB.[dbo].[##CrossTab]') AND xType='U') BEGIN
      PRINT 'There is an error in Parameter #3.'
      RETURN
   END

   -- Create a template for each CASE clause that will needed for the crosstab columns in the SELECT clause.
   -- The @AfterAgg gets added here, but the @BeforeAgg is inserted later.
   SET @AggregateFunction=Stuff(@AggregateFunction, Len(@AggregateFunction), 1, ' END)' + @AfterAgg)

   -- Determine whether or not to use a ' to surround the comparison values in the CASE clause.
   -- Varchar, Char and Date data types require the apostrophe whereas numbers do not require a delimiter.
   SELECT @Delimiter = Left('', COUNT(*))
       FROM ##CrossTab  WHERE IsNumeric(Cast(PivotColumn AS varchar(8000)))=0

   -- Generate all of the CASE statements needed in the SELECT clause.
   SELECT @SqlCase=Coalesce(@SqlCase,') +',' + Char(13)+'      [' + IsNull(Convert(varchar(8000), PivotColumn),'NULLS') + '] = ' +
         @BeforeAgg+Stuff(@AggregateFunction,CharIndex( '(', @AggregateFunction )+1, 0, 'CASE ' + @PivotColumn + ' WHEN '
         + @Delimiter + IsNull(Replace(Convert(varchar(8000), PivotColumn),'','''),'NULL') + @Delimiter + ' THEN ' )
      FROM (SELECT DISTINCT TOP 100 PERCENT PivotColumn FROM ##CrossTab ORDER BY PivotColumn) DistinctPivots

   ---Fix syntax of NULLS to say IS NULL instead of WHEN NULL.
   SET @ChrPos = CharIndex('[NULLS]',@SqlCase)
   IF @ChrPos>0 BEGIN
      SET @ChrPos = CharIndex('(CASE ',@SqlCase,@ChrPos)
      SET @SqlCase = Stuff (@SqlCase,@ChrPos+6,0,'WHEN ')
      SET @ChrPos = CharIndex(' WHEN ',@SqlCase,@ChrPos+10)
      SET @SqlCase = Stuff (@SqlCase, @ChrPos+1,9+2*Len(@Delimiter),'IS NULL')
   END

   DROP TABLE ##CrossTab

   -- Insert the CASE statements into the SELECT clause that was provided by the user.
   SELECT @QueryTemplate=Stuff(@QueryTemplate, CharIndex(' FROM ', @QueryTemplate), 0, @SqlCase + Char(13) + '  ')
   IF Len(@QueryTemplate)=8000
      PRINT 'WARNING: The length of the query string is too long!'
   PRINT @QueryTemplate
SET Ansi_Warnings ON
GO
************************************

   This example using the pubs database illustrates the point that a function can be placed around the aggregate function in parameter #2.

CODE

EXEC spCrossTabPrint
'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'
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 title

   This example illustrates that complex parameters (including a computed pivot column) are handled properly.

CODE

EXEC spCrossTabPrint
   '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',
   'avg(case when price<5 then 5 else price end)',
   'round(price*qty/100,0)*100'
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
   This example from the Northwinds database illustrates that even pivot columns that contain embedded single quotes are also handled properly.

CODE

DECLARE @C varchar(200)
SET @C='C'
SET @C= 'SELect LastName FROM Northwind.dbo.Employees e INNER JOIN Northwind.dbo.Orders o ON (E.EmployeeID=O.EmployeeID) where ShipName<'+@C+' GROUP BY LastName'
EXEC spCrossTabPrint
  @C,
  'count(lastname)',
  'Left(ShipName,8)'
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
   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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close