Pivot table in DataGrid

[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:

[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.

[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.