concatenation / group by problem

Hello does anybody knows a simple SQL answer on the following question:
presume the following table:
reference.|.productID..|..description
C1.........|... 2211....|...10F 15V
C3.........|... 2211....|...10F 15V
C6.........|... 1392....|...22F 10V
R3.........|... 0100....|...10K
R4.........|... 0100....|...10K
R2.........|... 0130....|... 4K7

The result should be...

productID..|.. references..|.. description..|.. amount
2211.......|.. C1,C3......|.. 10F 15V...|.. 2
1392.......|.. C6..........|.. 22F 10V...|.. 1
0100.......|.. R3,R4......|.. 10K.........|.. 2
0130.......|.. R2..........|.. 4K7.........|.. 1

Seems to be an ordenairy SUM and GROUP BY query
but the problem is the concatenation of the references.
Im not sure this is possible with pure SQL
Who has the solution?

Thanks
RobertNo, this is not generally possible.
But you may get close, e.g. when you have an upper bound on the number of rows per group.
This is actually a FAQ; see also the thread in http://www.dbforums.com/showthread.php?postid=4507450

Assuming an upper bound of 3, the following would give something close to what you want (where tbl is your table):SELECT A.productID, A.description,
min(
A.reference ||
CASE WHEN B.reference IS NULL THEN ' ' ELSE
', ' || B.reference ||
CASE WHEN C.reference IS NULL THEN ' ' ELSE
', ' || C.reference END END)
AS references
FROM tbl AS A
LEFT OUTER JOIN tbl AS B
ON A.productID = B.productID
AND A.description = B.description
AND A.reference < B.reference
LEFT OUTER JOIN tbl AS C
ON A.productID = C.productID
AND A.description = C.description
AND B.reference < C.reference
GROUP BY A.productID, A.description|||Hi Peter,

Many thanks for your advice.
This is an SQL solution indeed, but for my application,
where probably 40 references can exist for one component type, it doesnt fit.
I have to search for script or programming solutions.
As the matter of fact Cosmo suggested a VBA script on the Access forum that is worth to take a look at.
I'm not experienced in VBA but I managed to make it work.
Best regards

Robert (edadcon)|||yes, it's really difficult in general

however, it's trivially easy in MySQL, which has the GROUP_CONCAT function

why did i mention MySQL? because you posted in the non-specific SQL forum without mentioning which database system you're using

:)|||oops you're perfectly right.
I used access to build this database but we do use MYSQL as well!
I'd better chosen the last one instead. Probably I will switc anyway, it is not so hard to convert the access datyabase to MySQL.
Best regards
Robert (edadcon)