GridView Layout/Output

Question

I have an Website using ASP.Net 2.0 with SQL Server as Database and C# 2005 as programming language. In one of the pages I have a GridView with following layout.

Date -> Time -> QtyUsed

The sample values are as follows: (Since this GridView/Report is generated for a specific month only, I have extracted and displaying only the Day part of the date ignoring the month and year part.

01 -> 09:00 AM -> 05 
01 -> 09:30 AM -> 03
01 -> 10:00 AM -> 09
02 -> 09:00 AM -> 10
02 -> 09:30 AM -> 09
02 -> 10:00 AM -> 11
03 -> 09:00 AM -> 08
03 -> 09:30 AM -> 09
03 -> 10:00 AM -> 12

Now the user wants the layout to be like:

Time        01 02 03 04 05 06 07 08 09 
-------------------------------------------------------------------------
09:00 AM -> 05 10 08
09:30 AM -> 03 09 09
10:00 AM -> 09 11 12

The main requirement is that the days should be in the column header from 01 to the last date (the reason why I extracted only the day part from the date). The Timeslots should be down as rows.

From my experience with Excel, the idea of Transpose comes to my mind to solve this, but I am not sure.

Please help me in solving this problem.

Thank you.

Lalit Kumar Barik

Answer 1

In SQL Server, there is a PIVOT function that may be of use.

The MSDN article specifies usage and gives an example.

The example is as follows

Table DailyIncome looks like

VendorId   IncomeDay  IncomeAmount
---------- ---------- ------------
SPIKE      FRI        100
SPIKE      MON        300
FREDS      SUN        400
SPIKE      WED        500
...

To show

VendorId   MON         TUE         WED         THU         FRI         SAT         SUN

---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
FREDS      500         350         500         800         900         500         400
JOHNS      300         600         900         800         300         800         600
SPIKE      600         150         500         300         200         100         400

Use this select

SELECT * FROM DailyIncome
PIVOT( AVG( IncomeAmount ) 
FOR IncomeDay IN
 ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) AS AvgIncomePerDay

Alternatively, you could select all of the data from DailyIncome and build a DataTable with the data pivoted. Here is an example.

Answer 2

You will have to generate the dataset accordingly. I am guessing you are doing some kind of grouping based on the hour so generate a column for each hour of the day and populate the dataset accordingly.