Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

Concatanating 2 or more rows

Hi, Sounds simple but I can not figure this out.
I have 5 rows of data sharing a common id in my table.

ID NAME
22 Rick
22 John
22 Paul
22 Tom
22 Mary

The result I want on 1 line is:
Rick, John, Paul, Tom and Mary.

How can I make this so?

RickI am using CR XI, if this data is in details, go to details, select section expert, select format with multiple columns, you should see a layout tab pop up, select the size and direction you want your data to flow in, also, you may select format groups with multiple columns. If this is not what you were looking for... you can do this. Create a formula.
data1 &" "& data2 &" "& data3 &" "& data4 etc...|||Group on ID.
Create 3 formulas:
1) place in group header, suppress the formula
whileprintingrecords;
stringvar names := "";

2) place in details, suppress the section
whileprintingrecords;
stringvar names;
names := names & ", " & {table.field);

3) place in group footer
whileprintingrecords;
stringvar names;
mid(names, 2)|||One minor typo; the group footer formula should say
mid(names, 3)
or you'll have a leading space.

I've not addressed your requirement to replace the last comma with the word 'and', but you can probably work that one out yourself.

Comparing two tables with different fields

I have two tables which only have one key field in common. I want to synchronise them in the sense that the key items from one table also end up in the second table. I dont know how to do this. This is the situation on the tables:

table1
Fruit | color | taste |
Apple | Red | sweet |
pear | brown | sour |
orange| orange | sweet|
kiwi | brown | sweet

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4

Field1 is present in both tables but has more records in the first table than in the second. I want items in table1 that aren't present in table 2 to be added to table2. In other words, if more fruit is listed in table A I want it to be added to table 2. In this case, the orange and the kiwi should be added to the second table:

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4
orange| NULL | NULL
kiwi | NULL | NULL

Con someone help me?insert into table2 (fruit)
select fruit from table1
where not exists (select 1 from table2 where fruit=table1.fruit)

or

insert into table2 (fruit)
select fruit from table1
where fruit not in (select fruit from table2)