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