# Thread: concatenation / group by problem

1. Registered User
Join Date
Oct 2004
Posts
13

## Unanswered: 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.....|....10µF 15V
C3...........|.... 2211.....|....10µF 15V
C6...........|.... 1392.....|....22µF 10V
R3...........|.... 0100.....|....10K
R4...........|.... 0100.....|....10K
R2...........|.... 0130.....|.... 4K7

The result should be...

productID..|.. references..|.. description..|.. amount
2211.........|.. C1,C3........|.. 10µF 15V....|.. 2
1392.........|.. C6.............|.. 22µF 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.
I´m not sure this is possible with pure SQL
Who has the solution?

Thanks
Robert
Last edited by Edadcon; 01-10-06 at 19:56.

2. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
No, 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.

Assuming an upper bound of 3, the following would give something close to what you want (where tbl is your table):
Code:
```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```
Last edited by Peter.Vanroose; 01-11-06 at 04:03.

3. Registered User
Join Date
Oct 2004
Posts
13
Hi Peter,

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

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

5. Registered User
Join Date
Oct 2004
Posts
13
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