SQL: how to concatenate results against a single user?

Question

username      Name                    Skills
zainniazi     Zain Khan NiaZi         C
zainniazi     Zain Khan NiaZi         C++

How to get result in a single line where skills are concatenated? Like:

username      Name                    Skills
zainniazi     Zain Khan NiaZi         C,C++

Please provide a schema of the database and a query that you've tried. — STLDev, Mar 30 '14 at 15:40
Have a look at this question http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Jeff, Mar 30 '14 at 15:51

score 2 · Accepted Answer · answered Mar 30 '14 at 16:03

Try this:

SQL SERVER

SELECT
      T1.username
    , T1.Name
    , Skills = STUFF((
          SELECT ',' + T2.Skills
          FROM TableName T2
          WHERE T1.username = T2.username
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM TableName T1
GROUP BY T1.username,T1.Name
ORDER BY T1.username

See SQL SERVER example in SQL Fiddle.

MYSQL

SELECT username,Name,GROUP_CONCAT(Skills) 
FROM TableName
GROUP BY username,Name

See MySQL example in SQL Fiddle.

Result:

USERNAME    NAME              SKILLS
zainniazi   Zain Khan NiaZi   C,C++

SQL: how to concatenate results against a single user?

1 Answers1