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?

Magog

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

Selwyn

I have no idea how this works but it worked. Amazing! Thank you. – Souffle

How can I replace the comma with a carriage return line feed? I tried replacing ', ' with CHAR(13)+(Char10) but it kept replacing them with "#x0D;" – Dorotheadorothee

@Dorotheadorothee Please post a new question with your query and what you are trying to do. :) – Selwyn

Oddly, I had to concatenate an extra space in front of the selected field to get the whole value ' '+isNull(n5.myField1, n5.myField2) + '|' (it was coming back with the first character truncated) – Newsprint

a good answer, but extremely slow to run with a lot of rows. SQL Server 2017 added a new function STRING_AGG which we can use for this and is many, many times faster than FOR XML. I'll post a solution using STRING_AGG as a new answer. – Preserve

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.

Preserve

This is what I originally used but it shows duplicate string values. For example, if one player has multiple scores of the same value it lists all of them. The XML one above takes those out. I would love to have a version like yours to work with since I understand more about how it works and I would trust something I understand more but I can't have the duplicates unfortunately. – Workbag

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>

Treva

string_agg is an Oracle function; not exists in mssql. – Antibes

@Antibes STRING_AGG is not an Oracle function. It's a PostgreSQL one (and now a SQL Server one as of SQL Server 2017). – Anastos

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.

Attestation

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

Bedard

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

Nernst

Please consider adding some explanation to this. – Photoconductivity