Is there more efficient method to do a DataTable Unpivot?-VBForums
Results 1 to 4 of 4

Thread: Is there more efficient method to do a DataTable Unpivot?

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2009
    Posts
    35

    Is there more efficient method to do a DataTable Unpivot?

    Hello,

    I have two datatables that look like this.

    Code:
            Dim dt As New DataTable
            dt.Columns.Add("COL1")
            dt.Columns.Add("COL2")
            dt.Columns.Add("COL3")
            dt.Columns.Add("COL4")
            dt.Columns.Add("COL5")
    
            Dim dt2 As New DataTable
            dt2.Columns.Add("NAME")
            dt2.Columns.Add("VALUE")
    I insert a datarow into the first table.

    Code:
            Dim dr As DataRow = dt.NewRow()
            dr("COL1") = "Hello"
            dr("COL2") = "My"
            dr("COL3") = "Name"
            dr("COL4") = "Is"
            dr("COL5") = "Jlimited"
            dt.Rows.Add(dr)
    COL1 COL2 COL3 COL4 COL5
    Hello My Name Is Jlimited
    What I want to is unpivot the datatable into the second datatable.

    NAME VALUE
    COL1 Hello
    COL2 My
    COL3 Name
    COL4 Is
    COL5 Jlimited
    I have the following code that does what I want, but I have to loop through each datarow in the datatable and then through each data column within a given row.

    Code:
            For Each r As DataRow In dt.Rows
                For Each c As DataColumn In r.Table.Columns
                    Dim dr As DataRow = dt2.NewRow()
                    dr("NAME") = c.ColumnName
                    dr("VALUE") = r(c.ColumnName)
                    dt2.Rows.Add(dr)
                Next
            Next
    I am looking for a more efficient method to unpivot the datatable.
    I have implemented the same process in T-SQL, but I want to avoid uploading my first datatable to a stored procedure, just to download the results to the second.

    Been reading about LINQ a little bit and I think it is possible to do this using it, not sure of the syntax as LINQ is new to me. Any help would be appreciated.

    Thanks
    Jlimited

  2. #2
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,242

    Re: Is there more efficient method to do a DataTable Unpivot?

    8 lines of code seems pretty efficient to me! LINQ would (possibly) reduce the lines but would almost certainly slow the procedure so it kinda depends what you mean by 'efficient'.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    31,100

    Re: Is there more efficient method to do a DataTable Unpivot?

    LINQ will be slower, though you might get it down to a single, hard to read, line.

    By the way, does the order of the lines in the second table matter? For example, the way you have it, you write each field of row 1, then each field of row 2, and so on. Could you write field one for all rows, then field two of all rows, and so forth? I'm not sure that it would be any more efficient to do so, but it might give you more options.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    Member
    Join Date
    Mar 2009
    Posts
    35

    Re: Is there more efficient method to do a DataTable Unpivot?

    Quote Originally Posted by dunfiddlin View Post
    8 lines of code seems pretty efficient to me! LINQ would (possibly) reduce the lines but would almost certainly slow the procedure so it kinda depends what you mean by 'efficient'.
    Thanks for the feedback...I will stick with the code that I have.

    Thanks
    jlimited

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.