如何将逗号分隔值插入sql server 2008中的行 [英] how to insert comma separated values to rows in sql server 2008
问题描述
| SomeID | OtherID |数据
+ ---------------- + ------------- + --------- ----------
| abcdef -..... | cdef123 -... | 18,20,22
| abcdef -..... | 4554a24 -... | 17,19
| 987654 -..... | 12324a2 -... | 13,19,20
i想要这样
SomeID OtherID DataItem
1 9 18
1 9 20
1 9 22
2 8 17
2 8 19
3 7 13
3 7 19
3 7 20
4 6
9 11 1
9 11 2
9 11 3
9 11 4
< blockquote>你可以这样做:转换逗号分隔数据用于选择的行的列 [ ^ ]将告诉你如何。
但是首先将它存储起来通常是一个坏主意!在列中使用逗号分隔数据通常表示数据库设计存在缺陷,并且该列应该是一个单独的表,其中外键返回到原始数据库。它可能看起来更复杂,但它可以节省大量的时间,精力和工作。如果您的数据库仍在开发中,我会更改它而不是解决限制。
| SomeID | OtherID | Data
+----------------+-------------+-------------------
| abcdef-..... | cdef123-... | 18,20,22
| abcdef-..... | 4554a24-... | 17,19
| 987654-..... | 12324a2-... | 13,19,20
i want like this
SomeID OtherID DataItem
1 9 18
1 9 20
1 9 22
2 8 17
2 8 19
3 7 13
3 7 19
3 7 20
4 6
9 11 1
9 11 2
9 11 3
9 11 4
You can do it: Converting comma separated data in a column to rows for selection[^] will show you how.
But it's generally a bad idea to store it in the first place! Using comma separated data in a column is normally a sign that the DB design is flawed, and that the column should have been a separate table with a foreign key back to the original. It may seem more complex, but it saves a lot of time, effort and work later on. If your DB is still in development, I'd change it instead of working round the limitations.
这篇关于如何将逗号分隔值插入sql server 2008中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!