如何使用逗号分隔符检索列的值. [英] How to Retrieve value of a column with comma(,) separator.
本文介绍了如何使用逗号分隔符检索列的值.的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
先生
我有以下格式的表格.
tblOrder
----------
CustomerId产品名称
===================
1块香皂
2冷饮
1糖
1牛奶
2个饼干
现在我想写一个查询,将显示结果,如
客户ID产品名称
1块肥皂,糖,牛奶
2杯冷饮,饼干
先生,请帮我.
谢谢
Sir
I have table in the Following Format.
tblOrder
----------
CustomerId ProductName
========== ==========
1 Soap
2 Cold-Drinks
1 Sugar
1 Milk
2 Biscuits
Now i want to write a query which will display the result like
CustomerID ProductName
1 Soap,Sugar,Milk
2 Cold-Drinks,Biscuits
Plz help me Sir.
Thank You
推荐答案
尝试一下
SELECT GROUP_CONCAT(ProductName) FROM tblOrder GROUP BY CustomerId
如果您使用的是SQL 2005及更高版本,则可以像这样的东西:
If you are using SQL 2005 and above you could do something like this:
SELECT DISTINCT customerId, (SELECT CONVERT(VARCHAR(MAX), (SELECT LTRIM(UPPER(ISNULL(ProductName,'')) + ',')
FROM tblOrder o
WHERE o.CustomerId = orders.CustomerId
FOR XML PATH('')))) AS Products
FROM tblOrder orders
ORDER BY CustomerId
或使用STUFF()
OR using STUFF()
SELECT DISTINCT customerId, STUFF((SELECT ',' + ProductName FROM tblOrder FOR XML PATH('')), 1, 1, '') AS Products
FROM tblOrder orders
ORDER BY CustomerId
这篇关于如何使用逗号分隔符检索列的值.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文