Multiple SQL data rows to one row in ASP.Net GridView

Yesterday I came across one of those moments where you know something can be coded the way you want, but for the life of you, you can't think of how to do it or find how to do it anywhere.

The concept is this:

You have a table similar to this

Date	Group	Value
01/01/10	A	1
01/01/10	B	5
01/01/10	C	3
01/01/10	D	12
01/01/10	E	1

After running it through a gridview you want it to display like this:

Date	A	B	C	D	E
01/01/10	1	5	3	12	1

To achieve this the SQL statement is the key to making life easy. Here is a sample of what you can do.


<asp:SqlDataSource ID="connection1" runat="server"
ConnectionString="<%$ ConnectionStrings:Connection1%>"
SelectCommand="SELECT date,
SUM(CASE group WHEN 'A' Then Value END) As ValueA,
SUM(CASE group WHEN 'B' Then Value END) As ValueB,
SUM(CASE group WHEN 'C' Then Value END) As ValueC,
SUM(CASE group WHEN 'D' Then Value END) As ValueD,
SUM(CASE group WHEN 'E' Then Value END) As ValueE
From Table
Group by date
Order by date desc">


Now with the grid view you can just call your alias for each value:

<asp:GridView ID="GridView1" runat="server" DataSourceID="connection1" AutoGenerateColumns="false" AlternatingRowStyle-CssClass="even" RowStyle-CssClass="odd" AllowPaging="true" PageSize="5" GridLines="None">
<Columns>
<asp:BoundField DataField="date" DataFormatString="{0:d}" HeaderText="Date" />
<asp:BoundField DataField="ValueA" HeaderText="A"/>
<asp:BoundField DataField="ValueB" HeaderText="B"/>
<asp:BoundField DataField="ValueC" HeaderText="C"/>
<asp:BoundField DataField="ValueD" HeaderText="D"/>
<asp:BoundField DataField="ValueE" HeaderText="E"/>
</Columns>
</asp:GridView>



Good luck with your coding.

Share on Facebook Tweet Me