Flip a SQL table around

I have a sql table that looks like this

Id

Name

Value

1

First name

Bob

1

Last name

Johns

2

First name

John

2

Last name

Smith

3

First name

Bill

3

Last name

Gates

I want to return the data like this:

Id

First name

Last name

1

Bob

Johns

2

John

Smith

3

Bill

Gates

 

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s