Display Tabulated Data (VS2008 ASP.NET C#)

[Shaitan00]

This is going to be fun to explain... I have a DATABASE that contains the following data:

-DATE--------TIME----LEVEL
19/11/2008 - 8:00am - 05
19/11/2008 - 9:00am - 10
20/11/2008 - 8:00am - 06
20/11/2008 - 9:00am - 11
21/11/2008 - 8:00am - 12

So there is a column for DATE, a column for TIME, and a column for LEVEL. However, I need to display the data in a different format, specifically something like this


------------8:00am--9:00am
19/11/2008 -- 05 --- 10
20/11/2008 -- 06 --- 11
21/11/2008 -- 12

Notice that I have changed the orientation of the data, instead of having a row per Date/Time I now have columns that are TIME and rows that are DAYS and the intersection is the corresponding LEVEL.

Is there an easy way to transform the original data from the Database into such a format so I can use somekind of element to display it to the user? Like a gridview or whatever works best?

Any help would be greatly appreciated.
Thanks,

[Thread1]

if you are using sql server 2005/2008 you may use "cross-tab"/pivot in your query. however, a normal cross-tab query requires a pre-determined list of columns and it seems that you would have time values other than 8:00 AM and 9:00 AM. in that case, google around for "sql server 2005/2008 dynamic pivot"

hth

[Shaitan00]

Actually - the TIMES are not dynamic, I only want 8:00, 12:00, 18:00, and 23:00 (nothing else), so there will be a SET number of columns.

I've been playing with PIVOT and can't see to understand how it works - can you help nudge me in the right direction?
Thanks,

[Thread1]

you may just try this


SELECT [Date], MAX([8:00]) AS [8:00], MAX([12:00]) AS [12:00], MAX([18:00]) AS [18:00], MAX([23:00]) AS [23:00]
FROM <source Table>
PIVOT (
MAX([Level])
FOR ([Time]) IN ([8:00], [12:00], [18:00], [23:00])
) AS p1
GROUP BY [Date]


hth

[toraj58]

is it possible to use pivot in SQL SERVER 2000 also?

[Thread1]

since PIVOT is new to SQL Server 2005, i think it is going to be a complex query for SQL Server 2000.