Is there more efficient method to do a DataTable Unpivot?

[jlimited]

Hello,

I have two datatables that look like this.

        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.

        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.

        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

[dunfiddlin]

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

[Shaggy Hiker]

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.

[jlimited]

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