none
How To Transpose DataTable and Load Data Into Datagridview RRS feed

  • Question

  • Hi There,

    Straight to my question; I need to select EmpID, EmpName, Attendnace From Datatable 'def' and then load this data into datagridview. There are 100 EmpIDs and for each ID attendance data will be 30 or 31 days depending on the days of month and this will result into 30 or 31 rows for each empID and make a total of 31*100=3100 rows.

    WHAT I WANT IS THAT:

    On each row there appears EmpID and this ID's attendance data into 30 or 31 columns so there will appear only 30 or 31 rows with attendance record in front of each EmpID.

    Is that possible? If yes, then please help me with it I am in a big trouble.

    Thank you.

    Friday, April 20, 2018 10:54 AM

All replies

  • Yes theoretical it is possible and there are even two properties to make it faster going. 

    But it is a kind o humiliating the person who have to use it. 

    Simply do it for one user at a time where you set the EmpID in the Where clause of the select. 


    Success
    Cor

    Friday, April 20, 2018 11:22 AM
  • I understand what you mean by humiliating part but that is required reporting format. Can you p;ease help me with the code?
    Friday, April 20, 2018 11:46 AM
  • It is in fact the code on this page of our website and set before the setting of the datasource supendlayout and after that resumelayout.

    https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.resumelayout(v=vs.110).aspx

    We had no simple code like you need, so here copied and removed some rows.

    Done is Project -> Add New DataBase -> DataSource and then the selection

    Public Class Form1
         Private Sub Form1_Load(sender As System.Object,
                               e As System.EventArgs) Handles MyBase.Load
            Dim dt As New NorthwindDataSet.CustomersDataTable
            Using da As New NorthwindDataSetTableAdapters.CustomersTableAdapter
                da.Fill(dt)
            End Using
    DataGridView1.Suspendlayout
            DataGridView1.DataSource = dt.DefaultView
    DataFridView1.Resumelayout
        End Sub
    End Class
    


    Success
    Cor


    Friday, April 20, 2018 12:24 PM
  • It isn't clear to me what you are asking for. Can you show a few lines from the DataTable and then what you want the output to look like in the DataGridView? It sounds like you want to do a Pivot on the DataTable but more info would be helpful.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 20, 2018 1:02 PM
  • Thanks for asking Paul. Here's the code and the output that I want:

    Code:

            con.Close()
            Dim cmdslc As New SqlCommand("SELECT EmpID, EmpName, Attendance_Type FROM Attendance WHERE dateselect BETWEEN '" & dtfrm.Value.Date & "' AND '" & dtto.Value.Date & "' ORDER BY EmpID ASC ", con)
            con.Open()
                Dim abcslct As New SqlDataAdapter(cmdslc)
                Dim defslc As New DataTable
                abcslct.Fill(defslc)
                dgvsmry.DataSource = defslc

    Suppose user wants the data between dates 12-Apr-2018 to 15-Apr-2018, following data will be loaded (since I can't upload picture I try make the datatable):

    EmpID EmpName Attendance_Type

    5001    ABC         Present

    5001    ABC         Absent

    5001    ABC         Present

    5001    ABC       Present

    5002     DEF      Present

    5002      DEF      Present

    SO ON.....................

    ABOVE IS NOT REQUIRED.

    THE WAY THIS DATA IS NEEDED TO BE DISPLAYED IS SHOWN BELOW

    EmpID     EmpName       Attendance

    5001        ABC                 Present   Absent     Present     Present    Present

    5002        DEF                 Present Present SO ON...................

    So in each row there appear the ID next to it name and then all of that ID's attendance (absent, present) all in one row instead of multiple row for each ID.

    Hope I made my problem clear.

    Thanks

    Saturday, April 21, 2018 6:44 AM
  • Here a simple (old) sample on our website. The datagrid is used but you can use a datagridview with the same result.

    http://www.vb-tips.com/RotateDataTable.aspx


    Success
    Cor

    Saturday, April 21, 2018 7:28 AM
  • Thanks for the reply. Let me see if I could make it work.
    Saturday, April 21, 2018 7:32 AM
  • I am clueless on how to make it work for me. The code just add columns and rows where I want to load rows and columns from datatable
    Sunday, April 22, 2018 12:40 PM
  • I am clueless on how to make it work for me. The code just add columns and rows where I want to load rows and columns from datatable

    Hi,

    Take a look at this link ,

    may be it help you :

    ADO.Net DataTable transpose

    an don't forget to download the Demo from here:

    Download source code

    So,, good luck.

    Bajtitou.



    • Edited by Bajtitou Sunday, April 22, 2018 1:45 PM
    Sunday, April 22, 2018 1:39 PM
  • Is there a date associated with each entry? There doesn't seem to be any particular order with respect to each EmpID. If there is another column, such as a day of the week or date, a pivot table would be relatively straightforward.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, April 22, 2018 4:09 PM
  • I am clueless on how to make it work for me. The code just add columns and rows where I want to load rows and columns from datatable
    Yes it makes a new table from the old data

    Success
    Cor

    Sunday, April 22, 2018 4:45 PM
  • ABOVE IS NOT REQUIRED.

    THE WAY THIS DATA IS NEEDED TO BE DISPLAYED IS SHOWN BELOW

    EmpID     EmpName       Attendance

    5001        ABC                 Present   Absent     Present     Present    Present

    5002        DEF                 Present Present SO ON...................

    So in each row there appear the ID next to it name and then all of that ID's attendance (absent, present) all in one row instead of multiple row for each ID.

    Hope I made my problem clear.

    Thanks

    Hi NEMPH,

    According to your description, you want to combine multiple row into one row, I suggest you to do this like this. Then you can use this query to load data into datagridview. For example that this table name Test4

    SELECT DISTINCT EmpID,EmpName,
    Attendance = substring( ( SELECT ', ' + Attendance
    FROM test4 T1
    WHERE T1.EmpID = T2.EmpID FOR XML path(''), elements
    ),2,500)
    FROM test4 T2

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, April 25, 2018 9:10 AM
    Monday, April 23, 2018 7:27 AM
    Moderator
  • It does not work in my case because I created datatable from a sql query.

    Dim cmd as new Sqlcommand("SELECT * FROM Table",con)

    Dim abc as new sqldataadapter(cmd)

    Dim def as new datatable

    abc.fill(def)

    How to implement your code in this scenario?

    THanks

    Saturday, August 25, 2018 11:30 AM