This article uses the table we created in Part 1, called CUBE. Data is usually normalized into columns. For example, suppose we have a table of appointments. The table consists of 2 columns, DATE and APPOINTMENT. A common request is to display the data in a monthly calendar format. That is “pivot” the columns into a matrix. The matrix’s rows are the week number (52 weeks in a year) and the columns are the days in a week (Sun Mon…Sat). The monthly calendar is a simplified case of the pivot problem, as we know how many columns there are (7 days in a week) and we know their order (Sun Mon…Sat). Below we will go thru an example where we do not know either.
REQUIREMENTS
Previously, we displayed sales data in 3 columns: region, employee ID and sum of sales. Now our sales manager wants region in the Y vertical axis and employee ID in the X horizontal axis. That is one column for region and multiple columns for employee ID. Moreover, he doesn’t want the X and Y axes to always be region and employee ID. In the web page, we provide 3 input select boxes. The first select box determines the X axis: region or employee ID. We pass this value into the @X parameter. The second select box determines the Y axis: region or employee ID. We pass this value into the @Y parameter. The third select box determines the sort order: ascending sales, descending sales, or default. We pass this value into the @orderby parameter. The code is listed below.
<Our Reporting Stored Procedure>
CREATE PROCEDURE CubePivotSP
(
@method varchar(50)=’main’
,@startdate datetime=’1900/1/1′
,@enddate datetime=’2100/1/1′
,@group varchar(5000)=null
,@orderby varchar(5000)=’region,empid’
,@X varchar(50)=’empid’
,@Y varchar(50)=’region’
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @temp varchar(8000)
if @method=’drone’ begin
selectx=case @x when ‘region’ then region when ’empid’ then empid end
,y=case @y when ‘region’ then region when ’empid’ then empid end
,sumSales=sum(N)
from (
select
region=case when patindex(‘%region%’,@group)>0 then region else ” end
,empid=case when patindex(‘%empid%’,@group)>0 then cast(empid as varchar)
else ” end
,n
from cube
where [date] between @startdate and @enddate
) as a
group by region,empid
order by case @orderby when ‘desc’ then sum(N) end desc,case @orderby
when ‘asc’ then
sum(N) end,region,empid
return
end
if @method=’main’ begin
create table #X (X varchar(50),Y varchar(7000), sumSales int, I int identity(1,1))
insert into #X
exec CubePivotSP @method =’drone’,@x=@x,@y=@y,@startdate=@startdate, @enddate=@enddate,@orderby=
@orderby,@group=@X
create table #Y (X varchar(50),Y varchar(50), sumSales int, I int identity(1,1))
insert into #Y
exec CubePivotSP @method =’drone’,@x=@x,@y=@y,@startdate=@startdate, @enddate=@enddate,@orderby=
@orderby,@group=@Y
create table #R (X varchar(50),Y varchar(50), sumSales int, I int identity(1,1))
set @temp=@X + ‘,’ + @Y
insert into #R
exec CubePivotSP @method =’drone’,@x=@x,@y=@y, @startdate=@startdate, @enddate=@enddate,@orderby=@orderby,
@group=@temp
set @temp=null
update #x set @temp = y = isnull(@temp,”) + ‘,_’+ x+’=max(case x.i when
‘+cast(i as varchar)+’
then r.sumSales end)’
select top 1 @temp= y from #x order by i desc
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)’)
return
end
</Our Reporting Stored Procedure>