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

DRONE METHOD CODE BLOCK

This code block is similar to the "groupSales" block in article #2. It filters the data by @startdate and @enddate. If @group contains the word "region", it will return a row for every region. If @group contains the word "empid", it will return a row for every employee ID. It sorts the data based on the @orderby parameter. The main difference between it, and the one in the previous articles, is that it returns 2 new columns X and Y. The case statement "x=case @x when 'region' then region when 'empid' then empid end" assigns to X, the values the user requested for the X axis. Similarly, the second case statement assigns to Y, the values the user requested for the Y axis.

MAIN METHOD CODE BLOCK 

We first create the temp table #X. It has an identity column I. Column I increments from 1, 2, 3 and so on. It preserves the order of the X variables. For instance, the user wants to sort the pivot table by descending sales. We assign the parameter @orderby='desc'. We call on the drone: exec CubePivotSP @method ='drone', @x=@x,@y=@y,@startdate=@startdate,@enddate=@enddate,@orderby=@orderby,@group=@X. The clause "@group=@x" tells the drone to return a row for every @x value. In our example @x='empid' -- the drone will return a row for every employee ID. Moreover the rows will be sorted by descending sales and will be stored in the temp table #X. We then create the temp table #Y. We call on the drone method using this statement: exec CubePivotSP @method ='drone',@x=@x,@y=@y,@startdate=@startdate,@enddate=@enddate,@orderby= @orderby,@group=@Y. The clause "@group=@y" tells the drone to return a row for every @y value. In our example @y='region' -- the drone will return a row for every region. Moreover the rows will be sorted by descending sales and will be stored in the temp table #Y. Next we create the temp table #R. We concatenate @X and @Y into the @temp variable. We pass @temp into @group and execute the drone. It will return the body of our pivot table. It will return a row for every @X and @Y combination. In our example, it will return a row for every employee ID and region combination.

OUTPUT OF #X

X    Y    Sales   I

12    44  1

32    36  2

31    24  3

1     18  4

2      6  5

21     4  6

33     4  7

13     2  8

OUTPUT OF #Y

X    Y    Sales   I

  West    64  1

  South   46  2

  North   24  3

  East    4  4

OUTPUT OF #R

X    Y    Sales   I

12   South    44  1

32   West     36  2

31   West     24  3

1    North    18  4

2    North     6  5

21   East      4  6

33   West      4  7

13   South     2  8

Why did we create #X and #Y?  We need #X and #Y because they contain the identity columns for sorting. The basic statement is:

selectr.y

  ,x1=max(case x.i when  1 then r.sumSales end)

  ,x2=max(case x.i when  2 then r.sumSales end)

  ,x3=max(case x.i when  3 then r.sumSales end)

  ,x4=max(case x.i when  4 then r.sumSales end)

  ,x5=max(case x.i when  5 then r.sumSales end)

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)

OUTPUT OF BASIC SQL STATEMENT

Y    X1 X2  X3  X4  X5

West   NULL   36     24    NULL   NULL

South  44     NULL   NULL  NULL   NULL

North  NULL   NULL   NULL  18     6

East   NULL   NULL   NULL  NULL   NULL

In the select clause, "r.y" contains the region values. In the first case statement, "x.i" refers to the identity column of the temp table #X. For example, employee #12 has the most sales with 44. He belongs in the first column. We wrap the CASE statement with the MAX function. This was necessary because we're grouping over "r.y". We group in order to return a row for every Y or region value. We order by "max(y.i)". "y.i" refers to the identity column of the temp table #Y. In our example, West region has the highest number of sales. Its data belongs in the first row. We wrap it with the MAX function because SQL requires an aggregate function when we’re grouping. There are 2 unresolved issues with our code. One, it uses the generic column labels of Y, X1, X2 ,X3 ,X4 ,X5. Two, it has a fixed number of columns. We will use dynamic SQL to build a select statement that will re-label the columns and will display a column for every employee ID value.