How to merge multiple rows in one single column field with given condition in SQL SERVER 2008 [duplicate]

Question

I am using SQL Server 2008 and I need to implement this :-> I get a result like this :

Table:-sql query ..............

 acc_no           name
   001-000001       John
   001-000001       Bob
   001-000001       James
   001-000002       Sam
   001-000002       Bin 
   001-000002       Dus

So, the condition is that; multiple persons can have same acc_no. So i want following result:

 acc_no           name
   001-000001       John,Bob,James
   001-000002       Sam,Bin,Dus

There are other conditions for displaying the results but I got stuck in displaying this format.

Answer 1

How about something like

DECLARE @Table TABLE(
        acc_no VARCHAR(50),
        name VARCHAR(50)
)
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'John'
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'Bob'
INSERT INTO @Table (acc_no,name) SELECT '001-000001', 'James'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Sam'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Bin'
INSERT INTO @Table (acc_no,name) SELECT '001-000002', 'Dus'

--Concat
SELECT  t.acc_no,
        stuff(
                (
                    select  ',' + t1.name
                    from    @Table t1
                    where   t1.acc_no = t.acc_no
                    order by t1.name
                    for xml path('')
                ),1,1,'') Concats
FROM    @Table t
GROUP BY t.acc_no

SQL Fiddle DEMO

Answer 2

Try this one -

DECLARE @temp TABLE
(
      ID VARCHAR(20)
    , Name VARCHAR(50)
)

INSERT INTO @temp (ID, Name)
VALUES 
    ('001-000001', 'John'),
    ('001-000001', 'Bob'),
    ('001-000001', 'James'),
    ('001-000002', 'Sam'),
    ('001-000002', 'Bin'),
    ('001-000002', 'Dus')

SELECT t.ID, STUFF((
    SELECT ', ' + t2.Name
    FROM @temp t2
    WHERE t.ID = t2.ID
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
    SELECT DISTINCT ID 
    FROM @temp
) t

Output -

ID                   
-------------------- -------------------
001-000001           John, Bob, James
001-000002           Sam, Bin, Dus

Answer 3

 select acc_no,stuff((SELECT distinct ', ' + cast(name as varchar(10))
       FROM yourtable t2
       where t2.acc_no = t1.acc_no
       FOR XML PATH('')),1,1,'') 
       from yourtable t1
       group by acc_no