2

I am new to writing SQL scripts (at least anything more than SELECT * FROM X). I have run into a problem grouping a table by one value, and then joining values from another column into a single string.

I would like to perform a group by on a temp table, and then concatenate values of a column in the group together.

The table variable (edit), @categoriesToAdd, data structure is [SubscriberId int, CategoryId int].

What I am trying to do is something like (My understanding is that CONCAT is the bit missing from MSSQL): 

SELECT SubscriberId, 
       CONCAT(CONVERT(VARCHAR(10), CategoryId) + ', ') as categoriesAdded
FROM   @categoriesToAdd
GROUP BY SubscriberId

The concatenated category IDs for each subscriber would then look something like: 0001, 0002, 0003, 0004

Thanks!

Marek
  • 90
  • 1
  • 7
  • You search for `GROUP_CONCAT(MySQL)/LISTAGG(Oracle)/string_agg(Postgresql)` equivalent for MS SQL Server. I suggest using `STUFF + FOR XML`. More info and comparison **[Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/)** – Lukasz Szozda Mar 02 '16 at 11:42

1 Answers1

3

In sql server you can use FOR XML PATH

select  SubscriberId,
categoriesAdded=Stuff((SELECT ',' + CAST(CategoryId as VARCHAR(255)) FROM catsToAdd t1 WHERE t1.SubscriberId=@categoriesToAdd.SubscriberId
 FOR XML PATH (''))
             , 1, 1, '' )
from @categoriesToAdd as catsToAdd
GROUP BY SubscriberId
Marek
  • 90
  • 1
  • 7
Mihai
  • 26,325
  • 7
  • 66
  • 81
  • @categoriesToAdd in the where statement "WHERE t1.SubscriberId = @categoriesToAdd.SubscriberId" is getting the error: "Must declare the scaler variable '@categoriesToAdd'" – Marek Mar 02 '16 at 11:51
  • @Marek is that a temp table or a table variable?Because table variables have scope within the statement they were defined. – Mihai Mar 02 '16 at 11:53
  • I declared it like declare @ categoriesToAdd table ( SubscriberId int, CategoryId int ) Sorry if I asked the question wrong, I can change it! – Marek Mar 02 '16 at 11:54
  • @Marek You need to run this query in the scope where that table was declared,probably immediately after insert – Mihai Mar 02 '16 at 12:06
  • I got it by saying "@categoriesToAdd as catsToAdd" and then using that in the WHERE statement I questioned earlier in the comments. Change the answer to reflect that and I will accept this – Marek Mar 02 '16 at 12:59
  • @Marek Fell free to edit my answer,but its kinda strange I expected it to work as is. – Mihai Mar 02 '16 at 13:01