ASP.NET SQL Query help required

Question

I have a result like this from a sql query

Month    Day  Customer     Item
------------------------------------------
January   1   John         Pencil
January   1   Jack         ---
January   1   Steve        Stapler
January   2   John         ---
January   2   Jack         ---
January   2   Steve        VisitingCard
January   3   John         ---
January   3   Jack         Marker
January   3   Steve        ---
January   4   John         ---
January   4   Jack         ---
January   4   Steve        ---
January   5   John         Scrapbook
January   5   Jack         ---
January   5   Steve        ---
....
....
....
February  1   John         ---
February  1   Jack         ---
February  1   Steve        Marker

I'd like this to be represented in an asp.net gridview as below

                             January
Customer 1 2 3 4 5 6 7 8 9 10 11 ... 25 26 27 28 29 30 31
---------------------------------------------------------
John     P - - - N - - - - -- --     -- -- -- -- -- -- --
Jack     - - M - - - - - - -- --     -- -- -- -- -- -- --
Steve    S V - - - - - - - -- --     -- -- -- -- -- -- --
                             February
Customer 1 2 3 4 5 6 7 8 9 10 11 ... 25 26 27 28
------------------------------------------------
John     - - - - - - - - - -- --     -- -- -- --
Jack     - - - - - - - - - -- --     -- -- -- --
Steve    M - - - - - - - - -- --     -- -- -- --

How can I get this done either in SQL Server 2005 or in asp.net 2.0?

Please give the sample query for PIVOT as it requires some aggregate function to be used.

Answer 1

What you require is the Pivot command. I'm not familiar with the command's usage, but geekswithblogs has a good tutorial on how to use it.

Answer 2

I think you want to look at using the PIVOT command in your SQL query.

Answer 3

You cannot (cleanly) achieve this using the PIVOT function as the PIVOT function requires that you pre-define the columns that are output.

Since months have varying numbers of days you would have to write 12 queries.

IMO pivoting should always be done as part of the UI. Aggregation is an appropriate thing to do at the SQL end though

EDIT: If you explain how your data grid is actually built (i.e. where the P etc come from) then I can help generate you some appropriate SQL.

Answer 4

You might want to use a tool like DevExpress which provides you with a pivot table. You can then bind the pivot table to your data and it does the work for you.