1

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.

---UPDATE

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

Print(@Cats)
UserSN
  • 953
  • 1
  • 11
  • 33
  • 1
    Possible duplicate of [How to concatenate many rows with same id in sql?](http://stackoverflow.com/questions/35172956/how-to-concatenate-many-rows-with-same-id-in-sql) – Serg Nov 20 '16 at 19:15

2 Answers2

0

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.

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18
  • If I add `FOR XML Path` at the end of my query how can I remove the & & & tags just so it displays values? – UserSN Nov 20 '16 at 19:20
  • You haven't shown any of your web application code, so your question isn't really addressable. In fact, it sounds like a very different question than the one about concatenating rows. – rd_nielsen Nov 20 '16 at 19:25
  • My web application just gives me an area to insert a SQL query. When using SQL Management studio if I add `FOR XML Path` it does combine all rows into one but adds XML formatting where I just want values in a comma delimited string. – UserSN Nov 20 '16 at 19:28
  • 1
    You need more than just "FOR XML PATH" to generate a comma-delimited string. Numerous examples can be found with a web search. Here's one: https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ – rd_nielsen Nov 20 '16 at 19:35
  • thanks for your input the link has both the category & sub-category in my case I just need the subcategories going to try and modify their example for my needs. Thanks – UserSN Nov 20 '16 at 19:50
0

This accomplished my desired result.

SELECT CategoryName + ', ' AS [text()] 
FROM BND_ListingJunction_testing j
JOIN BND_ListingCategories c on c.CatID = j.Junc_CatID
WHERE Junc_LID=2
FOR XML PATH ('')
UserSN
  • 953
  • 1
  • 11
  • 33