How to display the specific values of a column in GridView Horizontally?

Question

I cannot explain further in the title so ill explain it here more.

This is my current interface in my gridview:

---------------------------------------------------------------------------
 Section   |   Exam   |   Normal Values   |   Result    |   Result Date   |
---------------------------------------------------------------------------
           | Calcium  |    NormalValue1   |   Result1   |    1-1-2014     |
Chemistry  |--------------------------------------------------------------|
           |  Sodium  |    NormalValue2   |   Result2   |    1-2-2014     |
---------------------------------------------------------------------------

I need to make it look like this:

---------------------------------------------------------------------------
 Section   |   Exam   |   Normal Values   |   1-1-2014   |    1-2-2014    |
--------------------------------------------------------------------------|
           | Calcium  |   NormalValue1    |    Result1   |                |
Chemistry  |--------------------------------------------------------------|
           | Sodium   |   NormalValue2    |              |     Result2    |
--------------------------------------------------------------------------|

Here's a print screen for better view: http://prntscr.com/4re3on

I need to display the Dates horizontally with the results below them. I get the data via stored procedure. I tried rotating the GridView into columns but it does not look right. How can i do this?

Here is my code:

Private Sub LoadGrid()
    Dim o_Dataset As New DataSet()

    Using sqlConn As New SqlConnection(DataSource.ConnectionString)
        Using sqlCmd As New SqlCommand()
            Dim sqlAdapter As New SqlDataAdapter(sqlCmd)
            sqlCmd.CommandText = "Station.dbo.[sp_Nurse_GetPatient_LabResult_NormalValues_Tabular_New]"
            sqlCmd.CommandType = CommandType.StoredProcedure
            'sqlCmd.Parameters.Add(New SqlParameter("@labsectionid", "H"))
            sqlCmd.Parameters.Add(New SqlParameter("@HospNum", Session.Item("HospNum")))
            sqlCmd.Connection = sqlConn
            sqlConn.Open()
            Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
            sqlReader.Close()
            sqlAdapter.Fill(o_Dataset)
            grdReports_H.DataSource = o_Dataset.Tables(0)
            grdReports_H.DataBind()
            GroupGridView(grdReports_H.Rows, 0, 3)
            sqlConn.Close()

        End Using
    End Using
End Sub

This code just hides my unwanted data

Protected Sub OnRowDataBound_H(sender As Object, a As GridViewRowEventArgs)
    If a.Row.Cells(0).Text = "A" Then
        a.Row.Visible = False
    End If
End Sub

This code just groups my Data, like the one displayed under section (Chemistry):

Private Sub GroupGridView(gvrc As GridViewRowCollection, startIndex As Integer, total As Integer)
    If total = 0 Then
        Return
    End If
    Dim i As Integer, count As Integer = 1
    Dim lst As New ArrayList()
    lst.Add(gvrc(0))
    Dim ctrl = gvrc(0).Cells(startIndex)
    For i = 1 To gvrc.Count - 1
        Dim nextCell As TableCell = gvrc(i).Cells(startIndex)
        If ctrl.Text = nextCell.Text Then
            count += 1
            nextCell.Visible = False
            lst.Add(gvrc(i))
        Else
            If count > 1 Then
                ctrl.RowSpan = count
                GroupGridView(New GridViewRowCollection(lst), startIndex + 1, total - 1)
            End If
            count = 1
            lst.Clear()
            ctrl = gvrc(i).Cells(startIndex)
            lst.Add(gvrc(i))
        End If
    Next
    If count > 1 Then
        ctrl.RowSpan = count
        GroupGridView(New GridViewRowCollection(lst), startIndex + 1, total - 1)
    End If
    count = 1
    lst.Clear()
End Sub

This is my aspx file:

    <style type="text/css">
  .hiddencol
  {
    display: none;
  }
</style>

   <asp:GridView  OnRowDataBound = "OnRowDataBound_H" ID="grdReports_H" AutoGenerateColumns="False" runat="server"   CellPadding="4" EnableModelValidation="True" ForeColor="#333333" style="text-align: center">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <EditRowStyle BackColor="#999999" />
                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <SelectedRowStyle  BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
       <Columns>
           <asp:BoundField  DataField="labsectionid" HeaderText="SectionID_H" ItemStyle-Width ="200px" ItemStyle-CssClass="hiddencol" HeaderStyle-CssClass="hiddencol" />
           <asp:BoundField DataField="Section"  ItemStyle-Height="10px"  HeaderText="Section" ItemStyle-Width="40px" ItemStyle-HorizontalAlign="right" ItemStyle-VerticalAlign="Top" />
           <asp:BoundField DataField="Exam" HeaderText="Exam" ItemStyle-Width="150px" />
           <asp:BoundField DataField="NormalValue" HeaderText="Normal Values" ItemStyle-Width="150px" />
           <asp:BoundField DataField="Result" HeaderText="Result" ItemStyle-Width="150px" />
           <asp:BoundField DataField="ResultDate" HeaderText="Result Date" ItemStyle-Width="150px" />
       </Columns>

Answer 1

this answered my problem. By using proper for loops and conditions. Please refer to the link: Print Datagrid table with many column headers accross multiple pages in asp.net

Answer 2

Rather than having bound fields make use of Templatefield and have HTML table with the format you like inside that field. Make use of <%# Eval("Column")%> to display your data in this table.

Here's an example GridView. (NOTE: This is just an example may not work as is. Just get the idea)

<asp:GridView runat="server" ID="gvTest">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <table>
                            <tr>
                                <td>Section</td>
                                <td>Exam</td>
                                <td>Normal Values</td>
                                <td>1-1-2014</td>
                                <td>1-2-2014</td>
                            </tr>                            
                    </HeaderTemplate>
                    <ItemTemplate>
                            <tr>
                                <td><%# Eval("Column1") %></td>
                                <%--Add other columns from your DataSet/DataTable match the columns--%>
                            </tr>                      
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

Read more here.

http://msdn.microsoft.com/en-us/library/aa479353.aspx http://msdn.microsoft.com/en-us/library/bb288032.aspx

You could do the same with Repeater

UPDATE 1

How to dynamically change the header

Try this

Have a Literal control in your header (This control can spit out HTML during render)

<HeaderTemplate>
    <table>
       <asp:Literal ID="Literal1" runat="server"></asp:Literal>                                                       
</HeaderTemplate>

In your code behind do something like this in ItemCreated event

  protected void gvTest_ItemCreated(Object sender, EventArgs e)
    {

        // Get the header row.
        GridViewRow headerRow = gvTest.HeaderRow;

        // Get the Literal control from the header row.
        Literal ltHeader = (Literal)headerRow.FindControl("Literal1");

        if (ltHeader != null)
        {
            // Build this headerRow string variable based on the values you want
            string headerRow = "<td>Section</td><td>Exam</td><td>Normal Values</td><td>1-1-2014</td><td>1-2-2014</td>";
            ltHeader.Text = headerRow;
        }
    }

Answer 3

Another approach is give your grid to autocolumn true option, then make a dataset from back-end (c#) which transform your data as your grid datasource. Finally bind the grid to datasource.

Refer below code :- Is it possible to switch rows and columns in a datagridview?

DataTable oldTable = new DataTable();

...

DataTable newTable = new DataTable();

newTable.Columns.Add("Field Name");
for (int i = 0; i < oldTable.Rows.Count; i++)
    newTable.Columns.Add();

for (int i = 0; i < oldTable.Columns.Count; i++)
{
    DataRow newRow = newTable.NewRow();

    newRow[0] = oldTable.Columns[i].Caption;
    for (int j = 0; j < oldTable.Rows.Count; j++)
        newRow[j+1] = oldTable.Rows[j][i];
    newTable.Rows.Add(newRow);
}

dataGridView.DataSource = newTable;

Answer 4

I think, you can achieve that using PIVOT in SQL Server. PIVOT essentially converts row values into columns. Have a look at this http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query for quick introduction. By doing this way, you can get away with some of the UI code about transforming rows into columns. Transformed dataset will come straight from stored proc.