Change Rows to Clolumns in a Dataset?

[J Jones]

How can I do it?

 

[IbrahimMalluf]

If you are using SQL-Server then look up Pivot Tables in BOL.


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

 

[Miha Markic]

Hi,

In additon to Ibrahim.
There is no built-in support.
I guess you'll have to create adequate DataTable and then copy data manually
(in a loop).

 

[J Jones]

Thanks for the reply Miha. I am creating the new dataset and tranfering the
data. My code to do that is below and I am getting 'There is no Row at
position 0' when I run it. As you can tell from my code, I am new to all of
this. can you tell me where Im going wrong?


Dim ds As New DataSet
Dim reader As New System.IO.StringReader(sXML)
Dim dt As DataTable = New DataTable
Dim i As DataRow
Dim x As Integer
ds.ReadXml(reader)
ds.Tables.Add(dt)

For Each i In ds.Tables.Item("Result").Rows
dt.Columns.Add()
Next

For x = 0 To 9
dt.Columns.Item(x).ColumnName() =
Str(ds.Tables.Item(0).Rows.Item(x).Item(0))
Next x

dt.NewRow()

For x = 0 To 9
dt.Rows.Item(0).Item(x) =
ds.Tables.Item(0).Rows.Item(x).Item(1)
Next

 

[Miha Markic]

Hi,

Inline

Thanks for the reply Miha. I am creating the new dataset and tranfering the
data. My code to do that is below and I am getting 'There is no Row at
position 0' when I run it. As you can tell from my code, I am new to all of
this. can you tell me where Im going wrong?


Dim ds As New DataSet
Dim reader As New System.IO.StringReader(sXML)
Dim dt As DataTable = New DataTable
Dim i As DataRow
Dim x As Integer
ds.ReadXml(reader)
ds.Tables.Add(dt)

For Each i In ds.Tables.Item("Result").Rows
dt.Columns.Add()
Next

You can set column name within dt.Coloumns.Add() so you don't need the loop
below.

For x = 0 To 9
dt.Columns.Item(x).ColumnName() =
Str(ds.Tables.Item(0).Rows.Item(x).Item(0))
Next x

dt.NewRow()

For x = 0 To 9
dt.Rows.Item(0).Item(x) =
ds.Tables.Item(0).Rows.Item(x).Item(1)
Next

You have not added a row to dt actually to dt. You should do something like:

Dim dr As DataRow = dt.NewRow()
For x = 0 To 9
dr(x) = ds.Tables(0).Rows(x)(1)
Next
dt.Rows.Add(dr)

And a hint: maybe is better than hardcode the 0..9 to change to
0..ds.Tables("Result").Rows.Count-1