36

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
  • 242,637
  • 56
  • 362
  • 405
Michael Rowley
  • 627
  • 2
  • 8
  • 17

4 Answers4

59

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
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 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
10

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.

Geoff Griswald
  • 937
  • 12
  • 24
  • 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. – lberecek Jul 25 '23 at 14:42
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>
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
-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
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Tigran
  • 1
  • 2