本文介绍了SQL Server - 将行加入逗号分隔列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
假设我有一个看起来像这样的临时表:
Let's suppose I have a temporary table which looks like this:
+----+------+
| Id | Value|
+----+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
+----+------+
我希望我的桌子是这样的:
And I want my table to be like this:
+----+----------+
| Id | ValueList|
+----+----------+
| 1 | 1,2,3 |
| 2 | 1,2 |
+----+----------+
所以基本上我需要将我的值分组为逗号分隔的列表.我已经尝试了以下方法:
So basically I need to group my values as a comma separated list. I already tried the following:
SELECT Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable
GROUP BY Id
但我得到类似的信息:
+----+---------------------+
| Id | ValueList |
+----+---------------------+
| 1 | 1,1,1,1,1,1,... |
+----+---------------------+
我找不到我做错了什么.有人可以帮忙解决这个查询吗?或者指出我正确的方向?谢谢.
I cant find what I am doing wrong. Could someone help with this query? Or point me to a right direction? Thank you.
推荐答案
您缺少子查询中的条件.
You are missing the condition inside the sub query.
SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1 where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id
演示
这篇关于SQL Server - 将行加入逗号分隔列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！