Pivot Data in SQL and Display in a Flex Advanced Data Grid Part One
My latest app called for six months worth of data to be displayed in a table with the dates appearing as column headings rather than as data in columns. The data itself is stored on a SQL 2008 server as flat data in a Data Warehouse. So some sort of PIVOT will be required. The date heading must be dynamic and change as each month passes with the oldest date dropping out and the other dates shuffling to the left to accommodate the newest column / date. The data grid must also contain certain data that is not affected by date. So static data if you will.
So I have two problems to deal with.
1. Pivot the date’s column into a Header row.
2. Create a data grid that can handle this dynamic section and then combine it with static (non date related data).
In this post I will deal with Getting the data ready for Flex and ColdFusion in Part Two I will deal with Handling the data in the Flex app itself.
I decided to start with a Stored Procedure to pivot the data ready for consumption in Flex and ColdFusion.
So first of all I need to convert the Date information from a datetime to a VARCHAR and apply some formatting to the Date to make it easier to read as a Column Heading. I then need to do this for every instance of data that appears in the set. I did this by doing the following.
I first of all declared a column variable.
declare @columns VARCHAR(8000)
and declare my date range which want to be 6 months worth of data. So that is 5 months of data previous to the current one.
declare @startDate varchar(50)
declare @endDate varchar(50)
set @startDate = dateadd(month,datediff(month,0,getdate())-5,0)
set @endDate = dateadd(month,datediff(month,0,getdate()),0)
then Convert and Coalesce the data from the table it is contained in where my date range applies and group them by the Date.
select @columns = COALESCE( @columns + ‘,[' + CONVERT( VARCHAR, MonthDate, 111 ) + ']‘, ‘[' + CONVERT( VARCHAR, MonthDate, 111) + ']‘ )
MonthDate between @startDate and @endDate
group by MonthDate
Where the 111 formatting = yyyy/mm/dd
So with that bit of the stored procedure done my data that were in columns will now be headers when I pivot the data. So how did I do that.
I did it by creating a query string and executing that once the Convert and Coalesce and been completed.
declare @query VARCHAR(8000)
and setting the string to do the following
set @query = ‘select * from dbo.Table_Name PIVOT ( SUM(Tonnes) FOR [MonthDate] in (‘ + @columns + ‘)) as p WHERE Var1 LIKE ‘ + ”” + @InputVar1 + ”” + ‘ AND Var2 LIKE ‘ + ”” + @ InputVar2 + ”” + ‘ AND Var3 LIKE ‘ + ”” + @ InputVar3 + ”” + ”
Then executing it thus.
execute( @query )
Turning this flat data below
So doing this has converted the MonthDate column into Column headers and summed up all the Values of the Tonnes Column for a specific Customer and Item within a six month window.
I can now get this out of SQL using a straight forward CF Function and present it to Flex.
The function is simple enough.
<cffunction access=”remote” name=”getForecastData” returntype=”query” >
<cfargument name=”Arg1″ type=”string” required=”yes” />
<cfargument name=”Arg2″ type=”string” required=”yes” />
<cfargument name=”Arg3″ type=”string” required=”yes” />
<cfstoredproc procedure=”sp_FLEX_myProcedure” datasource=”#dataSource#” >
<cfprocparam variable=”@InputVar1″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments. Arg1 #” />
<cfprocparam variable=”@InputVar2″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments. Arg2 #” />
<cfprocparam variable=”@InputVar3″ type=”in” cfsqltype=”cf_sql_varchar” value=”#arguments.Arg3 #” />
<cfprocresult name=”result” />
So that’s the data ready for Flex it took me a lot of trial and error to get to this point and it may not be the most elegant way of preparing the data for what I need it to do but it certainly works for me. In part Two I will deal with extending an Advanced Data Grid to handle the pivoted data section and a Static Data section too.