mohamed soliman mohamed soliman - 1 year ago 107 Question

how to Flip dataset and display in datagridview

I try to flip dataset to display column as rows by using this code but it does not work :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button.Click
Dim ds2 As New DataSet
Dim dt2 As New DataTable
Dim com1 As String = "select col1,col2,col3 from table1"
ds2 = FlipDataSet(ds2)
Dim dp As New SqlDataAdapter(com1, conn)
DGV_lev1.DataSource = dt2.DefaultView
End Sub

and use this function to flip dataset :

Private Function FlipDataSet(old_DataSet As DataSet) As DataSet

Dim ds As New DataSet()
For Each dt As DataTable In old_DataSet.Tables
Dim table As New DataTable()
For i As Integer = 0 To dt.Rows.Count
table.Columns(0).ColumnName = "Fields"
If i = 0 Then
Continue For

table.Columns(i).ColumnName = "Customer " & i
End If
Dim r As DataRow
For k As Integer = 0 To dt.Columns.Count - 1
r = table.NewRow()
r(0) = dt.Columns(k).ToString()
For j As Integer = 1 To dt.Rows.Count
r(j) = dt.Rows(j - 1)(k)
Return ds
End Function

to make datagirdview display from this :

enter image description here

to this :

enter image description here

can anyone help me

thank you

Answer Source

Try this, it worked in a quick test I did:

Private Function Transpose(ByVal table As DataTable) As DataTable
    Dim flippedTable As New DataTable
    'creates as many columns as rows in source table
            Function(dr) New DataColumn("col" & table.Rows.IndexOf(dr), GetType(Object))
    'iterates columns in source table
    For Each dc As DataColumn In table.Columns
        'get array of values of column in each row and add as new row in target table
        flippedTable.Rows.Add(table.Select.Select(Function(dr) dr(dc)).ToArray)
    Return flippedTable
End Function