Flip a SQL table around

April 10, 2007

I have a sql table that looks like this

I want to return the data like this:

I am completely lost and don’t even know where to start or what to search for… Any ideas?

For the time being, I used code to generate the required results, here it is:

Dim ada As
New EntriesDataSetTableAdapters.ViewEntriesTableAdapter

Dim entries As EntriesDataSet.ViewEntriesDataTable = ada.GetDataByForm(Me.FormId)

Dim ada2 As
New FieldsDataSetTableAdapters.FieldsTableAdapter

Dim fields As FieldsDataSet.FieldsDataTable = ada2.GetDataByForm(Me.FormId)

Dim target As
New Data.DataTable

target.Columns.Add(“__id”)

‘create columns

For
Each row As FieldsDataSet.FieldsRow In fields.Rows

target.Columns.Add(row.Name)

Next

‘loop through the table

For
Each row As EntriesDataSet.ViewEntriesRow In entries.Rows

Dim rows As System.Data.DataRow() = target.Select(String.Format(“__id=’{0}’”, row.InstanceId))

If rows.Length > 0 Then

‘we arleady created the row, so just add the field

rows(0)(row.Name) = row.Value

Else

Dim newRow As Data.DataRow = target.NewRow

newRow(“__id”) = row.InstanceId.ToString

newRow(row.Name) = row.Value

target.Rows.Add(newRow)

End
If

Next

Posted in .net, Programming, SQL