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.