Alex P Alex P - 1 year ago 90
SQL Question

Select Statement pulls values from a junction table into 1 comma delimited string

I have the following query that is selecting the "CategoryName" from my Junction table and Category table.

SELECT CategoryName
FROM BND_ListingJunction_testing j
JOIN BND_ListingCategories c ON c.CatID = j.Junc_CatID
WHERE j.Junc_LID = 3

In SQL this works fine and displays X rows for any category that LID matches in my junction table.

My web application however is only reading the 1st result when creating a token. Is it possible to pull the result set for X rows and display them all in 1 row just comma delimited.

For example: if LID=1 has been categories 5 times the above query will return 5 rows one for each category that exists in my junction table.

I'd like to have a result set that looks something like this (in one column):

Category1, Category2, Category3, Category4, Category5

Instead of:

1 Category1
2 Category2
3 Category3
4 Category4
5 Category5

Again I'm using 5 as an example a record can have anywhere from 1-X categories.


I've tried the following that seems to work but does not add a , between the results only 1 at the beginning.

Declare @Cats as Nvarchar(max) = ','
Select @Cats = @Cats + CategoryName
From BND_ListingJunction_testing j
JOIN BND_ListingCategories c on c.CatID = j.Junc_CatID
Where j.Junc_LID = 3


Answer Source

For SQL Server, you can use the XML PATH statement to convert multiple rows into a serial string aggregate.

Postgres has a 'string_agg()' aggregate function that is much easier to use.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download