0

Pretty new to sql so im looking for a simple way to break a field with multiple strings that are comma delimited into new rows, for example:

 user    names
 ------------------------
 1         john, Mike, Steve
 2         chris, phil , mark

to 

 user    names
 -----------------------
  1       john
  1       mike
  1       steve
  2       chris
  2       phil
  2       mark
bigdowg
  • 379
  • 3
  • 5
  • 19
  • Storing values as csv is very bad db design. – Jens Apr 17 '15 at 12:45
  • Lesson one; don't store data like comma separated items. It will only cause you lots of problems. – jarlh Apr 17 '15 at 12:45
  • its a work db , i didnt do it. just trying to break it down for now. – bigdowg Apr 17 '15 at 12:46
  • 1
    @Jens, there are not `ultimate rules` in `SQL Server` - it always depends on the current circumstances. – gotqn Apr 17 '15 at 12:53
  • @gotqn actually in this case there really isn't a "it depends". Storing multiple values in a single intersection violates 1NF. When your database isn't even 1NF there is a major issue caused by bad design. – Sean Lange Apr 17 '15 at 13:26
  • @SeanLange You cannot tell what is in this case as this could be a `sample data` and you do not know what's the business logic. There is no point to argue here, I can give you some real examples from our company in chat or something if you want. – gotqn Apr 17 '15 at 13:30
  • @gotqn just because you do it at your company does not mean storing delimited data is a good idea. The OP states they have a column with delimited data. This is a poor design decision that they had to post here to get help because it is difficult to deal with. – Sean Lange Apr 17 '15 at 13:32
  • @SeanLange Of course it is not, but in some cases, it could be the silver bullet for major issue. – gotqn Apr 17 '15 at 13:34
  • @SeanLange just because it's a bad idea doesn't mean that it doesn't happen and that some people encounter it and have to somehow deal with it **:)** OP clarified that they didn't design it this way, they just want help in dealing with it, regardless of how ugly it is (and it is, nobody disagrees on that here) – asontu Apr 17 '15 at 13:43

0 Answers0