select /join with datatable

[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 ?

 

[Guest]

I am not quiet sure if I understood you correctly, are you looking for a SQL-String to do the job

Then try something like this

select t1.text as firstname, t2.text as lastname, t3.text as age fro
record t1 join record t2
on t1.key = t2.ke
join record t3 on t2.key = t3.ke
order by t1.ke

Mf

Martin
----- smoke wrote: ----

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

ke
fieldnam
tex

record examples

1,firstname,Ada
1,lastname,Smit
1,Age,3
2,firstname,Bo
2,lastname,Brow
2,Age,2


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

firstname lastname Ag
--------------------------
Adam , Smith , 3
Bob , Brown , 2


I know I can do this by using a oledbadapter(sqlstatement,thisConnection
bu
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

 

[Guest]

Ahem maybe better: But I am still not sure if that´s what you wanted

select t1.text as firstname, t2.text as lastname, t3.text as age fro
record t1 join record t2
on t1.key = t2.ke
join record t3
on t2.key = t3.ke
where
t1.fieldname = 'firstname' an
t2.fieldname = 'lastname' and
t3.fieldname = 'Age
order by t1.key

----- Chucker wrote: ----

I am not quiet sure if I understood you correctly, are you looking for a SQL-String to do the job

Then try something like this

select t1.text as firstname, t2.text as lastname, t3.text as age fro
record t1 join record t2
on t1.key = t2.ke
join record t3 on t2.key = t3.ke
order by t1.ke

Mf

Martin
----- smoke wrote: ----

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

ke
fieldnam
tex

record examples

1,firstname,Ada
1,lastname,Smit
1,Age,3
2,firstname,Bo
2,lastname,Brow
2,Age,2


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

firstname lastname Ag
--------------------------
Adam , Smith , 3
Bob , Brown , 2


I know I can do this by using a oledbadapter(sqlstatement,thisConnection
bu
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