Merge multiple rows into one column without duplicates

Question

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?

Answer 1

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

Answer 2

Previous accepted answer is superseded in SQL 2017 and up by STRING_AGG:

SELECT Player, STRING_AGG(Score,', ') FROM YourTable GROUP BY Player

No need to use awkward FOR XML syntax.

I ran this and the accepted answer side-by-side on 100K rows. Accepted answer took 90 seconds, the STRING_AGG version takes less than 1 second.

Answer 3

A bit late and slightly off-topic as for another RDBMS, but I found this thread searching for a solution to this problem in Postgres. I found one, so if anyone else needs to solve this problem in Pg:

SELECT string_agg(DISTINCT <column>,'delimiter') FROM <table> GROUP BY <column2>

Answer 4

In Sql server version upper than 2017 you can use below funtion instead of dealing with xml

STRING_AGG(CAST(AuthorName as nvarchar(MAX)),', ') 

Here we are doing cast to increase length of data that column can have.

Answer 5

For SQL Server, you can use the STRING_AGG with a subquery to only get unique values.

SELECT Player, STRING_AGG(Score,', ') FROM 
    (SELECT DISTINCT Player, Score FROM YourTable) t 
  GROUP BY Player

Answer 6

UPDATE AllNews 
    SET ArticleSource = pp.[NewsText]
  FROM AllNews AS an
  INNER JOIN (  select t1.Id,
  stuff((SELECT distinct '.' + t2.[Text]
           FROM NewsPhotos t2
           where t2.NewsId = t1.Id
           FOR XML PATH('')),1,1,'') as [NewsText]
             from AllNews t1
             group by t1.Id) as pp
  ON pp.Id = an.Id