>Hi All,
>
>Okay, In the calling vfp program, I create a huge temp table called #bptable. Then I want to call this stored procedure that gets information for several different tables, including another temp table. I take the result of that select statement, and build a string, because I use many records for the result to create one record in the temp table:
>
>

>result table
>st_id  name          place          time
>  1    First         Missoula       12:01 PM
>  2    Second        Billings       06:35 AM
>  5    Third         Canada         08:44 AM
>  8    First         Lincoln        08:00 AM
>
>
>New Temp Table
>newid  st_id  name     place     time     st_id1   name1      place1   time1   st_id2  name2     place2    time2
>  1      1    First    Missoula  12:01 PM   2      Second     Billings 06:35 AM  5     Third     Canada     08:44 AM
>  2      8    First    Lincoln    08:00 AM
>

>
>
>In vfp, I loop through the records and do an insert after each string is completely built.
>
>Is there a better way to do this?
>Can this string be built in SP?
>
>TIA
>Beth

Wouldn't something like this work?

select 
tab1.st_id as st_id, tab1.cname as cname, tab1.place as place, tab1.ctime as ctime,
tab2.st_id as st_id1, tab2.cname as cname1, tab2.place as place1, tab2.ctime as ctime1,
tab3.st_id as st_id2, tab3.cname as cname2, tab3.place as place2, tab3.ctime as ctime2
into #newTemp
from 
(select * from #result where cname = 'First') tab1 
left join
(select * from #result where cname = 'Second') tab2 on tab1.rtw_id = tab2.rtw_id
left join
(select * from #result where cname = 'Third') tab3 on tab1.rtw_id = tab3.rtw_id

Cetin