如何编写sql查询从sql server数据库表中选择multile记录为单个值？ [英] How do I write sql query for selecting multile record into single value from sql server database table?

问题描述

我有供应商列名称，有数据保存下面的模式

供应商

1 | 2 | 3

45 | 85 | 66

我希望例外输出是

供应商

1

2

3

45

85

66

I have vendor column name and there is data is saved bellow pattern
vendor
1|2|3
45|85|66
and I want excepted output is
vendor
1
2
3
45
85
66

推荐答案

简单的答案是：别。不要将数据存储为连接字符串，使用单独的表将供应商与他们销售的产品交叉引用。设置似乎更多的工作（它确实如此），但它会在以后节省大量时间 - 正如您开始看到的那样。现在改变它是很容易的 - 在你有大量的代码和数据建立之前 - 而不是决定你需要在以后有一个巨大的任务来解决它。



但是......如果你必须的话，你可以做到。它非常可怕，非常混乱：

将列中的逗号分隔数据转换为行以供选择 [ ^ ]



说真的，现在更改数据库布局和查询所有都变成简单的JOIN（SQL服务器非常非常擅长处理 - 字符串操作不是它的强项，结果非常慢）

The simple answer is: don't. Don't store data as concatenated strings, use a separate table to cross reference your vendors with the products they vend. It seems like more work to set up (and it is) but it saves a huge amount of time later - as you are starting to see. It's a lot easier to change that now - before you have a mass of code and data built up - than it is to decide you need to to it later when you have a huge task to sort it out.

But...you can do it, if you must. It's just horribly, horribly messy:
Converting comma separated data in a column to rows for selection[^]

Seriously, change your DB layout now and the queries all become simple JOINs (which SQL server is very, very good at processing - string manipulation is not its forte, and is very slow as a result)

这篇关于如何编写sql查询从sql server数据库表中选择multile记录为单个值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！