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?

Taryn · Accepted Answer · 2013-11-19T17:06:24.147

56

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

edited Nov 19 '13 at 17:06

answered Sep 28 '12 at 18:22

Taryn

234,956
54
359
399

7

I have no idea how this works but it worked. Amazing! Thank you. – Lukas Feb 11 '13 at 20:46
1

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;" – Osprey Mar 21 '13 at 15:34
1

@Osprey Please post a new question with your query and what you are trying to do. :) – Taryn Mar 21 '13 at 15:35
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) – gordon Nov 17 '17 at 19:48
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. – Geoff Griswald Sep 23 '20 at 14:32

dermesser · Answer 2 · 2013-05-17T10:34:20.660

8

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>

edited May 17 '13 at 10:34

answered Apr 12 '13 at 19:24

dermesser

93
1
7

string_agg is an Oracle function; not exists in mssql. – MarwaAhmad Feb 15 '16 at 13:02
3

@MarwaAhmad `STRING_AGG` is not an Oracle function. It's a [PostgreSQL one](https://www.postgresql.org/docs/current/static/functions-aggregate.html) (and now a SQL Server one as of SQL Server 2017). – jpmc26 Sep 21 '17 at 22:24

score 4 · Answer 3 · answered Sep 23 '20 at 14:39

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.

score -3 · Answer 4 · edited Jan 13 '19 at 06:23

-3

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

edited Jan 13 '19 at 06:23

Ajay2707

5,624
6
38
56

answered May 23 '18 at 18:16

Tigran

1
2

Please consider adding some explanation to this. – EJoshuaS - Stand with Ukraine May 23 '18 at 19:20

Merge multiple rows into one column without duplicates

4 Answers4

Linked