Changing vertical data into a table layout in .Net

Question

I have a SQL Server 2005 output like this:

Date | Result | Sum
-----|--------|-----
8/26 |   01   | 1549
8/26 |   02   | 7972
8/26 |   03   | 4502
8/27 |   01   | 1897
8/27 |   02   | 1649
8/27 |   03   | 7949
8/28 |   01   | 8542
8/28 |   02   | 5335
8/28 |   03   | 4445

and I want it to show up (in a GridView?) like this:

Result | 8/26 | 8/27 | 8/28
-------|------|------|------
  01   | 1549 | 1897 | 8542
  02   | 7972 | 1649 | 5335
  03   | 4502 | 7949 | 4445

My current attempt is trying to do this via a DataView that comes from a SqlDataSource as well as a new DataSet. I'm not having much luck though. The "Result" values can be dynamic and the dates will be dynamic.

Any tips?

Answer 1

Rather than trying to make the changes on the .NET side, modify your stored procedure to do a Pivot of the data, that will allow you to get the exact structure you want.

here is a MSDN article on PIVOT.

Answer 2

This is my solution. The trickiest part to get it to work was realizing that I had to use the WITH x as syntax because I've never heard of it before.

WITH x as (
 SELECT Days.Date, Disps.HisResult, SUM(CASE WHEN Historic.HisResult IS NULL THEN 0 ELSE 1 END) AS SumResult
 FROM (
  SELECT DISTINCT DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, -5, HisCallDate)), 0) as Date FROM Historic
 ) AS Days
 LEFT JOIN (SELECT DISTINCT roxcoDispCode as HisResult FROM RoxcoSystem.dbo.DispCodes) AS Disps ON 1=1
 LEFT JOIN Historic ON Disps.HisResult = Historic.HisResult AND Days.Date = DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, -5, HisCallDate)), 0)
 GROUP BY Days.Date, Disps.HisResult
)
SELECT * FROM x
PIVOT
(
  MAX(SumResult)
  FOR HisResult IN ([00], [01], [02], [03])
)
AS p