如何使用逗号分隔在Sql server中的单列中插入和检索多个值? [英] How to insert and retreive multiple values in single column in Sql server with comma separation ?
问题描述
我有一个SQL表MENU,其列为
Menu_id,Menu_Name,Rate
。
在前端用户可以选择n个菜单他们在复选框中选择(CheckBoxList)。我已经编写了代码来存储用户在MenuId中用逗号分隔的所有menuId(Tbl_Order:Order_Id,Customer_Id,MenuId)。
这是一个很好的方法来存储这些值这样的Sql server column?
稍后我还需要找到客户订购的所有菜单。那么如何从逗号分隔的MEnuID列中获取个人ID?
很快给我一个解决方案。
我最好的建议是学习如何规范化你的数据库。
你现在的设计将来会给你带来很多麻烦。
数据库规范化 [ ^ ]
3普通表格数据库教程 [ ^ ] < br $> b $ b
数据库规范化基础知识 [< a href =http://databases.about.com/od/specificproducts/a/normalization.htmtarget =_ blanktitle =新窗口> ^ ]
吨这个
选择Menu_id +','+ Menu_Name +','+从菜单中评价
我们要连接一个列行值成单行我们需要写这样的查询
DECLARE @ name NVARCHAR (MAX)
SELECT @ name = COALESCE ( @ name + ' ,',' ')+ UserName FROM UserDetails
SELECT UserNames = @ name
如果您观察到上述查询我使用函数COALESCE它返回其ar中的第一个非null表达式guments。
另一种方式
如果我们希望 连接行 列我们可以将 写为,如下所示
DECLARE @ name NVARCHAR (MAX )
SET @ name = ' '
SELECT @ name = @ name + ' ,' + UserName FROM UserDetails
SET @ name =子串( @ name , 2 ,(len( @ name )))
SELECT @ name as UserNames
查看此
I have a SQL table MENU with columns
Menu_id,Menu_Name,Rate
.
In the front end user can select n menus at their choice in checkboxes(CheckBoxList). I have written code to store all the menuId's for a user in a single column MenuId separated by commas (Tbl_Order: Order_Id,Customer_Id,MenuId).
Is it a good way to store these values like this in Sql server column?
Later I also need to find what all menus a customer has ordered. So how to Take individual ID's from comma separated MEnuID column?
Give me a solution soon.
My best advice is to learn how to normalize your database.
Your current design will give you lots of headaches in the future.
Database normalization[^]
3 Normal Forms Database Tutorial[^]
Database Normalization Basics[^]
try this
Select Menu_id+','+Menu_Name+','+Rate from Menu
we want to concatenate one column row values into single row we need to write query like this
DECLARE @name NVARCHAR(MAX) SELECT @name = COALESCE(@name + ',', '') + UserName FROM UserDetails SELECT UserNames = @name
If you observe above query I used function COALESCE it returns the first non-null expression among its arguments.
Another way If we want to concatenate rows of column we can write query like as shown below DECLARE @name NVARCHAR(MAX) SET @name='' SELECT @name = @name +','+UserName FROM UserDetails SET @name = Substring(@name, 2, (len(@name))) SELECT @name as UserNames
check this
这篇关于如何使用逗号分隔在Sql server中的单列中插入和检索多个值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!