Join question

[smoke]

I have an access table (control) that consists of fields:

key
fieldname
text

record examples:

1,firstname,Adam
1,lastname,Smith
1,Age,30
2,firstname,Bob
2,lastname,Brown
2,Age,25


Is there a way to join this field to itself on key so that the result
will be:

firstname lastname Age
---------------------------
Adam , Smith , 30
Bob , Brown , 25


I know I can do this by using a oledbadapter(sqlstatement,thisConnection)
but
Is there a way to do it with a selectCmd:

DataTable controlTable=dataSet1.Tables["control"];
string selectCmd;
DataRow[] controlRows;

selectCmd = ????????

controlRows = controlTable.Select(selectCmd);


If it cant be done this way, Is there another way ?

 

[Nicholas Paldino [.NET/C# MVP]]

Smoke,

What you are trying to do is not trivial. Basically, you will have to
select the records, and cycle through them, creating a new record in the new
table and setting the appropriate field, if it exists.

I am not sure if there is cross-tab functionality in SQL server, but if
there is, that is the kind of operation that you want to perform.

Hope this helps.