[SQL Server] Reduce and Merge duplicate rows

Question

Hi there,

I have a table with the following sampled data:

Name    Color

Alice   Green
Bob     Black
Chris   Green
Chris   Black     
David   Red
Peter   Blue
Simon   Blue
Simon   Red
Simon   Green

Ultimately, I want to reduce the table by consolidating the Color column like:

Name    Color

Alice   Green
Bob     Black
Chris   Green, Black
David   Red
Peter   Blue
Simon   Blue, Red, Green

such that Name can become unique.

The table has no PRIMARY KEY, I got as far as creating a new column using ROW_NUMBER to distinguishing duplicates but don't know what to do next.:

rownumber    Name    Color

1            Alice   Green
1            Bob     Black
1            Chris   Green
2            Chris   Black     
1            David   Red
1            Peter   Blue
1            Simon   Blue
2            Simon   Red
3            Simon   Green

Answer 1

Don't do this. Instead, normalize your tables further to e.g. a Person, Preference and a Color table (where Preference, if that is the right name for the relation, has foreign keys to Person and Color). This way, you avoid the risks of inconsistencies (you can make Person names unique if you like, but you should make Color names unique).

EDITED: if you're getting this from a join query I'll assume the data is reasonably consistent, so normalization isn't an issue. Would it be possible to change the join query to GROUP on Name instead? Much cleaner than hacking around a result set, really!

Answer 2

I have adopted the approach here

with a table variable to hold the temporary result set to work(hack) from.

Sorted!