Get data by comma seperate with same UserId

Question

I have table which have following fields.

• ID = Bigint (PK)
• USERID = bigint
• Area = Varchar(50)

Now Every User have 3 Areas in table (So there are 3 entry for USERid with different Area).

I need output as below:

Now In report I want to Display USERID and all Area with comma separated associated by his UserID as above.

I try with group by but I did not find any help.

Answer 1

For MS-SQL Server 2008, you can use the following XML hack.

SELECT DISTINCT t.USERID, REPLACE((
            SELECT Area AS 'data()'
            FROM table1 a
            WHERE a.USERID = t.USERID
            FOR XML path('')
            ), ' ', ', ') AS Areas
FROM table1 t

A little bit better would be to use STUFF instead of REPLACE

SELECT DISTINCT t.USERID, STUFF((
                SELECT Area AS 'data()'
                FROM table1 a
                WHERE a.USERID = t.USERID
                FOR XML path('')
                ) , 1, 1, '') AS Areas
FROM table1 t

If you have a lot of functions where you need to use this and there is a performance issue, then you better have a look at the GROUP_CONCAT string aggregate for SQL Server. This is a T_SQL function, you can download it from CodePlex here.

Answer 2

For MySQL, you can group your results by their USERID and and then use GROUP_CONCAT to concatenate all Area in the group. Something like this:

SELECT USERID, GROUP_CONCAT(Area) FROM table1 GROUP BY USERID;

For SQL Server, you might want to take a look at Simulating GROUP_CONCAT MySQL function in SQL Server?

Answer 3

select UserID,Area= ( SELECT Area + ',' from #table1 A1 where A.UserID =A1.UserID FOR XML PATH('')
) from #table1 A GROUP BY A.UserID