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?

Check this one out: http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function — Chris McCall, Aug 31 '09 at 20:31
@d03boy: "I do not know the column names ahead of time", you've got no other choice :( — Chris McCall, Aug 31 '09 at 22:06

score 3 · Accepted Answer · answered Aug 31 '09 at 20:22

3

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.

answered Aug 31 '09 at 20:22

Mitchel Sellers

62,228
14
110
173

(and I'm not very great with SQL) – Joe Phillips Aug 31 '09 at 20:38
If only SQL would just get better then I wouldn't have to :) – Joe Phillips Sep 01 '09 at 03:32
Well, you can do it with a Dynamic SQL statement, and there is even a way to do it with little risks. See this post - http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns. I have an article about it as well that will be published in SQL Server Magazine, but not until November.... – Mitchel Sellers Sep 01 '09 at 13:51

score 0 · Answer 2 · answered Sep 01 '09 at 22:19

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

Changing vertical data into a table layout in .Net

2 Answers2