Member Avatar for Texpert

Hi,

I need help solving this problem.
I have a dataset with four columns col1,col2,col3,col4 and the data could be
col1 col2 col3 col4
=====================================
t1 abc a 1
t1 xyz a 2
t1 mnp b 3
t2 abc 1
t2 kkl 2

I want to create a datagrid which will have, column headings from the col1 data (t1 and t2 as columns) starting from 2nd column in data grid
1st column heading should be blank (like in excel spreadsheet)
and under 1st column in the datagrid, col2 (from source dataset) values should appear as rows
something like this

t1 t2
===========================
abc
xyz
mnp
kkl

that's not it, now remaining two columns col3 and col4 from the source dataset are the attributes of rows, so somehow I need to split each cell under column t1 and t2 and show values of col3 and col4

so far I could get the column headings t1 and t2
by writing separate query for col2 I can even get the first column in datagrid populated but I am clueless how to get two attributes in one cell

any idea ?

thanks in advance

Dim MySQLString As String

        Dim objParam1 As SqlParameter
        Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Conn"))
        Dim dest As DataTable


        Try
            Session("Current_PackageId") = PackageNameDDL.SelectedItem.Value

            'MySQLString = "SELECT DISTINCT Component.Description,Song.SongTitle, "
            'MySQLString = MySQLString + " XREFCompSong.ComponentSeq, XREFCompSong.ComponentSide  "
            'MySQLString = MySQLString + " FROM XREFCompSong INNER JOIN"
            'MySQLString = MySQLString + " Song ON XREFCompSong.SongID = Song.PXSongID INNER JOIN"
            'MySQLString = MySQLString + " Component ON XREFCompSong.ComponentID = Component.ComponentID "
            'MySQLString = MySQLString + " WHERE (Component.PackageID  = @vPackageId)"

            MySQLString = "SELECT DISTINCT Component.Description"
            MySQLString = MySQLString + " FROM Component "
            MySQLString = MySQLString + " WHERE (Component.PackageID  = @vPackageId)"

            Dim MySqldataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQLString, MyConn)
            Dim MyDataSet As DataSet = New DataSet

            objParam1 = MySqldataAdapter.SelectCommand.Parameters.Add("@vPackageId", SqlDbType.Int)
            objParam1.Value = PackageNameDDL.SelectedItem.Value

            If MyConn.State = ConnectionState.Closed Then
                MyConn.Open()
            End If

            MySqldataAdapter.Fill(MyDataSet)

            dest = New DataTable("PivotTable")
            dest.Columns.Add("   ")

            Dim r As DataRow

            For Each r In MyDataSet.Tables(0).Rows
                dest.Columns.Add(r(0))
            Next

            DataGrid1.DataSource = dest
            DataGrid1.DataBind()

                       MyConn.Dispose()
            MyConn.Close()
        Catch ex As Exception
                     System.Diagnostics.Trace.WriteLine("[ShowBtnClick] Exception " & ex.Message)
        Finally
            MyConn.Close()
        End Try

Notice that I've commented out first query, because with that query I get error while creating columns that "column t1 is already in the dataset", since t1 repeates in the query result.

:sad: :sad:

  • 3 Contributors
  • 2 Replies
  • 893 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by JakeDB

Dani AI

Generated

A practical, keep-it-simple approach is to pivot the DataTable in memory: create a new DataTable whose first column is the row header (distinct values from col2) and add one column per distinct col1 (t1, t2). For each (col2,row) fill the cell by selecting matching source rows and concatenating col3/col4 (either as plain text like a (1) or with <br/> for line breaks). Check for existing column names before adding to avoid the "column already in the dataset" error that @Texpert ran into.

Private Function BuildPivot(src As DataTable) As DataTable
    Dim pivot As New DataTable("Pivot")
    pivot.Columns.Add(" ")

    Dim cols As New List(Of String)
    For Each r As DataRow In src.Rows
        Dim c1 = Convert.ToString(r("col1"))
        If Not cols.Contains(c1) Then
            cols.Add(c1)
            pivot.Columns.Add(c1)
        End If
    Next

    Dim rows As New List(Of String)
    For Each r As DataRow In src.Rows
        Dim c2 = Convert.ToString(r("col2"))
        If Not rows.Contains(c2) Then rows.Add(c2)
    Next

    For Each key As String In rows
        Dim dr = pivot.NewRow()
        dr(0) = key
        For i As Integer = 0 To cols.Count - 1
            Dim filter = String.Format("col1 = '{0}' AND col2 = '{1}'", cols(i).Replace("'", "''"), key.Replace("'", "''"))
            Dim matches() As DataRow = src.Select(filter)
            If matches.Length > 0 Then
                Dim parts As New List(Of String)
                For Each m As DataRow In matches
                    Dim a = If(IsDBNull(m("col3")), "", m("col3").ToString())
                    Dim b = If(IsDBNull(m("col4")), "", m("col4").ToString())
                    parts.Add(String.Format("{0} ({1})", a, b))
                Next
                dr(i + 1) = String.Join("<br/>", parts.ToArray())
            Else
                dr(i + 1) = String.Empty
            End If
        Next
        pivot.Rows.Add(dr)
    Next

    Return pivot
End Function

Bind the returned table to the DataGrid: DataGrid1.DataSource = BuildPivot(srcTable) then DataGrid1.DataBind(). To render the <br/> HTML inside cells, use a TemplateColumn with a Literal (or set the cell HTML in ItemDataBound) rather than a BoundColumn. If aggregation is required (sum/count) do the aggregate in the inner loop instead of string concatenation. For very large data sets a server-side solution (SQL PIVOT or dynamic SQL) or a commercial pivot-capable grid (as @JakeDB suggested) will scale better. This also helps the later poster @luciano_sunny who reported the same issue.

Member Avatar for luciano_sunny

Hi there did you get any reply from anywhere for this problem of yours because i am also stuck with almost same kind of thing.

Member Avatar for JakeDB

Sound like you need a real pivot table gird with some customization capabilities. There are a few of them on the market but usually quite pricy. You can try the VIBlend SuperGridView. http://www.viblend.com. Not too expensive and does a great job.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.