We help IT Professionals succeed at work.

add comma-delineated column into SQL output

Evan Cutler
Evan Cutler used Ask the Experts™
on
I have this SQL:
      SELECT A.PK, A.Title, LEFT(A.Text, 25) + '...', a.date_entered, C.Concentration
      from Advertisements A
      join  [Concentration-Advertisement] CA
      ON A.PK = CA.FK_Advertisement
      join Concentrations C
      ON CA.FK_Concentration = C.PK;

the C.Concentration field lists the field that the Advertisement is associated with.
what happens is rows repeat Advertisement info, because it is a 1 to many ratio.

Instead, I want the C.Concetration to represent a comma-delineated list of the concentrations for one row per advertisement.

Below is the table reference...
Advertisement
============
PK, Title, Text, Date

Concentration-Advertisement
========================
PK, FK-C, FK-A

Concentration
===========
PK, Concentration

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

Try

SELECT A.PK, MAX(A.Title) AS Title, LEFT(MAX(A.Text), 25) + '...' AS Text, MAX(a.date_entered) AS date_entered,
STUFF((SELECT ', '+C.Concentration AS[text()] FROM [Concentration-Advertisement] CA JOIN Concentrations C ON C.PK = CA.FK_Concentration WHERE CA.FK_Advertisement = A.PK FOR XML PATH('')), 1, 2, '') AS Concentration
FROM Advertisements A
GROUP BY A.PK;

/peter
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
And that's why your the sage :)

Thanks