如何在sql中基于id获取用逗号分隔的多个值 | 如何在sql中基于id获取用逗号分隔的多个值

本文介绍了如何在sql中基于id获取用逗号分隔的多个值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

输入赞

1 a

1 d

1 b

2 c

2 a

1 e

2 b

输出如

1 a，b，c，d，e

2 a，c，e

请帮我解决这个问题在此先感谢

Input Like
1 a
1 d
1 b
2 c
2 a
1 e
2 b

Output like

1 a,b,c,d,e
2 a,c, e

Please Help me to solve this Thanks in Advance

推荐答案

SELECT STUFF( (SELECT ',' + RTRIM(valueColumnName)
                              FROM tableName
               WHERE idColumnName = 1
               ORDER BY valueColumnName
               FOR XML PATH('')
          ),1,1,'') AS 'StringData'

或者

SELECT a.IdColumnName,
       STUFF( (SELECT ',' + RTRIM(ValueColumnName)
               FROM TableName b
	       WHERE a.IdColumnName = b.IdColumnName
	       ORDER BY ValueColumnName
   	       FOR XML PATH('')
	),1,1,'') AS 'StringData'
FROM TableName a
GROUP BY a.IdColumnName

SELECT
     GroupingColumnName,
     STUFF(
         (SELECT DISTINCT ',' + ListableColumnName
          FROM MyTable
          WHERE GroupingColumnName = t.GroupingColumnName
          FOR XML PATH (''))
          , 1, 1, '')  AS ItemsList
FROM MyTable AS t
GROUP BY GroupingColumnName

这篇关于如何在sql中基于id获取用逗号分隔的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！