sql-server - Concatenate string values in one row from multiple rows with constraints SSIS / SQL Server

Question

I need some help, I'm pretty new in SSIS and have some basics in SQL Server.

I have a SQL query within a package in SSIS, I tried various solutions (STUFF(), STRING_AGG(), SUBSTRING()...), but every time I got some errors.

I have a file source with data that looks like this:

Name,Active,AccountNr,Comment
Alex,30,895478548,Food,
Alex,50,895478548,Sport,
Alex,30,5544440000,Travel,
Fabien,15,4555555,Car,
Fabien,2500,63553336,Family,
Fabien,2500,4555555,Health,
Alex,30,895478548,Travel

I want to add the actives and concatenate string values of Comment column (which have the same Account number) in one row from multiple rows

For example, rows 4-6 have the same Account number, then we should get

:Fabien,2515,Family/health

as output.

标签： sql-serverssis

Answer 1

SSIS 不支持string_agg()吗？如果是这样，您可以这样做：

select name, account_number, string_agg(comment, '/') all_comments
from mytable
group by name, account_number