I have a result like this from an 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.
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.