Convert Rows to Column using COALESCE() function

CREATE TABLE [dbo].[tbl_name](

      [name] [varchar](50) NULL,

      [city] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c3')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'n', N'd1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'n', N'd2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e3')

DECLARE @eMailList nvarchar(max)

SELECT @eMailList = COALESCE(@eMailList + ';', '') +

  CAST(name AS nvarchar(max))

FROM tbl_name

Select @eMailList as eMailList

For Multiple Column:

SELECT name, city = STUFF(( SELECT ', ' + city

                            FROM dbo.tbl_name

                            WHERE name = x.name

                            FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'), 1, 2, '')

FROM dbo.tbl_name AS x

GROUP BY name