将多行合并为一列，不重复 [英] Merge multiple rows into one column without duplicates

问题描述

我正在处理将从表中收集数据并显示报告数据的查询.

I am working on a query that will collect data from a table and display the data for a report.

数据如下:

Player Score
001      10
001      20
002      20
002      20
001      10
002      10
003      20
002      20
001      10

我希望它像这样显示

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?

推荐答案

对于 SQL Server，您可以使用:

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

这篇关于将多行合并为一列，不重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！