SQLTeam.com | Weblogs | Forums

Concatenation of a field based by the grouping of another field

Transact-SQL
#1

hello,
i am struggling to concatenate a column based on the grouping of another column.
image

I have column ID and column Partners. i need to make a concatenation (column Concat). For each ID i need to concatenate all its partners. If i have blanks i need to exclude it (ID=B).
Any suggestion would be great.
Thank you in advance :slight_smile:

#2

Which specific version of SQL Server are you on? 2012? 2014? Whichever.

#3 

declare @alexa table(id varchar(50), partners varchar(50))
insert into @alexa
declare @alexa table(id varchar(50), partners varchar(50))
insert into @alexa
select 'A', 11 union
select 'A', 12 union
select 'A', 123 union
select 'C', 56 union
select 'C', null

select id,  STRING_AGG (partners, ',') as zz
from @alexa
where partners is not null
group by id
#4

i am using 2012

#5 

SELECT 
    a1.*,
    STUFF((SELECT '||' + partners
        FROM your_table_name a2
        WHERE a2.id = a1.id AND a2.partners <> ''
        ORDER BY a2.partners
        FOR XML PATH(''), TYPE).value('.', 'varchar(8000)'), 1, 2, '') 
    AS Concat
FROM  your_table_name a1
#6

thank you , it works :slight_smile: