SQL Server Techniques for Creating a Web Reporting Page — Part 3

We need to build a SQL statement that has a case statement for every X value. It should contain 8 case statements. It should contain: ,_12=max(case x.i when  1 then r.sumSales end),_32=max(case x.i when  2 then r.sumSales end),...,_13=max(case x.i when  8 then r.sumSales end). We can use a cursor or a while loop. I prefer to use an extension to the UPDATE statement because it performs this function in one step. The basic syntax is: UPDATE [table] SET @variable = [column] = [expression(@variable)]. The statement operates right to left. It calculates the expression, which is a function of the variable. It updates the column (of the first row) with the value of the expression. It then saves the value into the variable. It moves to the next row. It calculates the expression using the updated value of the variable. It updates the column (of the second row) and saves the new value into the variable etc.

In our code we're updating the column Y of the temp table #X. The right side of the statement the first row is translated as: [variable] + ‘,_’ + [x column] + ‘=max(case x.i when ‘ + [I column] + ‘ then r.sumSales end)’. On the first row, the variable @temp is null. As we’ve wrapped it with the isnull function, it returns a zero length string. Concatenate with a comma and an underscore. Concatenate with the value of the X column, which contains employee Ids. Concatenate with the MAX and CASE statement. And concatenate with the value of the I column, which contains the identity values. Column Y of the first row then holds the character string: ,_12=max(case x.i when  1 then r.sumSales end). This value is saved into the variable @temp. On the second row, @temp is concatenated with the new case statement. It does this for every row, so that the eighth row contains 8 case statements concatenated together. We save the case statements to the @temp variable. And execute our dynamic SQL: exec ('select '+@y+'=r.y'+@temp+' from #R as r join #X as x on r.x=x.x join #Y as y on r.y=y.y group by r.y order by max(y.i)').

EXAMPLE OUTPUT:

exec cubepivotsp @x='empid',@y='region',@orderby='desc'

REGION _12    _32 _31 _1  _2  _21 _33 _13 

West   NULL   36     24    NULL    NULL    NULL    4       NULL

South  44     NULL   NULL  NULL    NULL    NULL    NULL    2

North  NULL   NULL   NULL  18      6       NULL    NULL    NULL

East   NULL   NULL   NULL  NULL    NULL    4       NULL    NULL

exec cubepivotsp @x='region',@y='empid',@orderby='region,empid'

EMPID  _EAST   _NORTH   _SOUTH   _WEST

1  NULL    18   NULL NULL

12 NULL    NULL 44   NULL

13 NULL    NULL 2    NULL

2  NULL    6    NULL NULL

21 4       NULL NULL NULL

31 NULL    NULL NULL 24

32 NULL    NULL NULL 36

33 NULL    NULL NULL 4

This concludes our article about pivot/transpose. I wish to thank Brad McGehee, of SQL-Server-Performance.Com, for the opportunity to share my SQL techniques.

About the Author

Louis Duc Nguyen lives in Dallas, TX and has a BS in Engineering and an MBA from University of Texas at Austin and Dallas. He is currently working for a major airline. He specializes in creating data-entry and reporting applications. Please address comments and hot job tips to Louis Nguyen at louisducnguyen@hotmail.com.

Copyright © 2002-2003 Louis Duc Nguyen All Rights Reserved