Advice on "converting" one DataTable to a different structure?

  • Thread starter Thread starter Jim Bancroft
  • Start date Start date
J

Jim Bancroft

Hi everyone--

I have a one-row DataTable that I need to manipulate. It consists of a
series of three columns of related information. All the columns are
strings. For example--

Col1 Col1Descrip Col1Value Col2
Col2Description Col3Value <etc>
"AUTHOR" "email author" "John" "CC" "the
copyee" "Rebecca"

What I'd like to do is "pivot" the table, so to speak, so that it only has
three columns; one for the field name, the field description, and the value.
The single row I have now would be broken into separate rows of three
columns each. Again, here's an example of what I'd like see in the end:

NewCol NewColDescrip NewColValue
"AUTHOR" "email author" "John"
"CC" "the copyee" "Rebecca"

Is there an optimal way of doing this? I suppose I could create a new
DataTable with three columns, then walk through the original DataRow and
build a new row every three columns along....it just sounds a little
kludgey. I don't know, but I thought I'd ask if there were another, better
way to do it. Thanks for the advice.
 
Hi Jim,

Another way is to create XML transformation stylesheet, but I believe it
would be more complicated. I think they you are going it to do should be
fine
 
Jim,

Here a sample that I once made for it, however I don't see it in the
newsgroups on Google anymore so here it is again (a little bit changed).

\\\Needs only a project with a datagrid on a form
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet
Dim dt1 As New DataTable("Original")
ds.Tables.Add(dt1)
dt1.Columns.Add("Name")
dt1.Columns.Add("Adress")
dt1.Columns.Add("Age")
dt1.LoadDataRow(New Object() {"John", "23 noway", 12}, True)
dt1.LoadDataRow(New Object() {"Sally", "12 yesway", 34}, True)
dt1.LoadDataRow(New Object() {"Suzzy", "43 okway", 21}, True)
Dim dt2 As New DataTable("Reflection")
ds.Tables.Add(dt2)
For i As Integer = 0 To ds.Tables("Original").Rows.Count - 1
dt2.Columns.Add(i.ToString)
Next
For i As Integer = 0 To ds.Tables("Original").Rows.Count - 1
Dim dr As DataRow = ds.Tables("Reflection").NewRow
For y As Integer = 0 To ds.Tables("Original").Columns.Count - 1
dr(y) = ds.Tables("Original").Rows(y).Item(i)
Next
ds.Tables("Reflection").Rows.Add(dr)
Next
DataGrid1.DataSource = ds.Tables("Reflection")
End Sub
///

I hope this helps a little bit?

Cor
 
Val & Cor--

I appreciate the replies. Cor, I like what you've done above....I'll modify
it a bit and give it a shot.
Thanks again, guys.

-Jim
 
Back
Top