如何使用逗号分隔符检索列的值. [英] 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屋！