将多个行合并到一个列中，不需要重复

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?

有人知道怎么让这个工作吗?

3 个解决方案

#1

For SQL Server you can use:

对于SQL Server，您可以使用:

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

#2

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:

对于另一个RDBMS来说，有点晚，有点偏离主题，但是我发现这个线程在Postgres中搜索这个问题的解决方案。我找到了一个，所以如果有人需要解决这个问题

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

#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

将AllNews中的ArticleSource = pp.[NewsText]更新为内部连接(选择t1)。(选择不同的Id,东西。' + t2。[正文]来自NewsPhotos t2, t2。NewsId = t1。XML PATH(")的Id()，1,1， ")作为[NewsText]来自AllNews t1 group by t1.Id)作为pp.Id = an.Id