0

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
Chris
  • 657
  • 1
  • 9
  • 17
  • 1
    Why do you want to _denormalize_ your database? Normalized tables are normally considered a good thing. – Oded Jul 20 '10 at 08:59
  • Hmm... This is because the "table" above is actually a query result from JOINing a lot of views, and I'm looking at a complete overhaul if I am given the permission to. Still thanks for pointing out normalization is a good practice in DB world. I'm essentially not a DB programmer hence I'm not good at SQL at all embarrassingly speak. – Chris Jul 20 '10 at 09:06

2 Answers2

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!

Pontus Gagge
  • 17,166
  • 1
  • 38
  • 51
  • (Good heavens. I actually managed an American spelling of colour consistently. Now, **that's** will power! :-) – Pontus Gagge Jul 20 '10 at 09:05
  • Well, forgot to mention this is a result set stored in a table variable so not sure if normalising it would help. I just have to find ways to identify the duplicate rows and then use the coalesce function to build a string of all "Color"s and put it into a new column somehow but really dunno how to go about it. (I did try to impersonate as American by spelling colour as color, and that took me quite a bit of self control I have to say. I think my impersonation worked well :-) – Chris Jul 20 '10 at 09:11
0

I have adopted the approach here

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

Sorted!

Community
  • 1
  • 1
Chris
  • 657
  • 1
  • 9
  • 17