sqlsql-servert-sql

Merge multiple rows into one column without duplicates


I am working on a query that will collect data from a table and display the data for a report.

The data looks like this:

Player Score
001      10
001      20
002      20
002      20
001      10
002      10
003      20
002      20
001      10

I want it to display it like this

Player Score
001    10,20
002    10,20
003    20

But all I get is a combined list of all data in the score column like this

Player Score
001    10,20,10,10
002    20,20,10,20
003    20

Does anyone have an idea how to make this work?


Solution

  • For SQL Server you can use:

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