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?

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