ASP.NET SQL query

Question

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.

Glorfindel · Accepted Answer · 2022-11-12 15:05:16Z

4

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

edited Nov 12, 2022 at 15:05

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Jun 22, 2009 at 12:59

Gavin Miller

44k22 gold badges130 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Mortensen Over a year ago

The second link is (effectively) broken. It redirects to Geekswithblogs.net, the End of an Era.

Peter Mortensen · Accepted Answer · 2022-01-26 22:58:07Z

3

You cannot (cleanly) achieve this using the PIVOT function as the PIVOT function requires that you predefine 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.

edited Jan 26, 2022 at 22:58

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jun 22, 2009 at 13:07

Joel Mansford

1,3167 silver badges13 bronze badges

4 Comments

Akmal Over a year ago

I have 3 tables after joining which I get the four columns shown in the first table. I've given P, M, S, etc.,. for explaining the question here. In actual we'd be displaying small icons in the grid. (Using template columns depending upon the value, appropriate icon will be displayed. Say If the value is Pencil, a pencil icon will be displayed.)

Joel Mansford Over a year ago

So can a given customer have multiple items on a given date?

Akmal Over a year ago

No. Max. One record per customer on any given date.

Joel Mansford Over a year ago

I'm afraid in that case the resultset you're already returned is as much as you should do on the SQL side. You'll need someone better than me at ASP.NET / Linq to help further. Personally I would do this in SSRS as a Matrix, you would say repeat [month] per page and then set the columns to be [day]. Value would [item].

Josh · Accepted Answer · 2009-06-22 13:00:34Z

1

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

answered Jun 22, 2009 at 13:00

Josh

5903 silver badges4 bronze badges

Comments

macleojw · Accepted Answer · 2009-09-08 10:00:13Z

0

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.

answered Sep 8, 2009 at 10:00

macleojw

4,16910 gold badges48 silver badges63 bronze badges

Collectives™ on Stack Overflow

ASP.NET SQL query

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related