-1

How to merge data row into an object.

For example :

Declare @Email as varchar(max);

Select @Email=email from M_Employee

I want to merge all email into @Email with ';' as separator.

Timothy Varian
  • 25
  • 8
  • 5
    This is a very common question. Have a [Google](http://www.google.com/) of `STUFF`, `FOR XML PATH` and delimited string. if you get stuck with the syntax, edit your post with what you've tried. – Thom A Dec 21 '17 at 09:54

2 Answers2

0

Try this

select 
@Email=LEFT(l.list,LEN(l.list)-1)
FROM
(
SELECT  Email + '; ' AS [text()]
FROM M_Employee
FOR XML PATH('')
)l(list)
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
0

You you can use STUFF, with FOR XML:

SELECT @Email = STUFF((SELECT distinct ';' +
                        email 
                       FROM M_Employee AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

This will give you all the emails comma separated with ;.

  • @Downvoter, care to explain??!!!!!!!! whats wrong with the answer???? or show us your fancy answer instead!!!!!!!!!1 –  Dec 21 '17 at 10:13
  • The op wants the emails comma separated with `;` and this is what my answer does, then whats wrong????????!!!!!!!!!! –  Dec 21 '17 at 10:13
  • 1
    Somebody around here to Down vote without any reason. Leave it. Cheers...!+1 – DineshDB Dec 21 '17 at 10:36