如何将多行组合到SQL Server 2005中以逗号分隔的列表中? [英] How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
本文介绍了如何将多行组合到SQL Server 2005中以逗号分隔的列表中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
SELECT X,Y FROM POINTS
它返回如下结果:
XY
----------
12 3
15 2
18 12
20 29
我想将结果全部返回到一行,就像这样(适合在HTML< AREA>标记中使用):
XYLIST
----------
12,3,15,2 ,18,12,20,29
有没有一种方法可以使用SQL来完成这项工作?
解决方案
DECLARE @XYList varchar(MAX)
SET @XYList =''
SELECT @XYList = @XYList + CONVERT(varchar,X)+','+ CONVERT(varchar,Y)+','
FROM POINTS
- - 删除最后一个逗号
SELECT LEFT(@XYList,LEN(@XYList) - 1)
Right now, I have a SQL Query like this one:
SELECT X, Y FROM POINTS
It returns results like so:
X Y
----------
12 3
15 2
18 12
20 29
I'd like to return results all in one row, like this (suitable for using in an HTML <AREA> tag):
XYLIST
----------
12,3,15,2,18,12,20,29
Is there a way to do this using just SQL?
解决方案
DECLARE @XYList varchar(MAX)
SET @XYList = ''
SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS
-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)
这篇关于如何将多行组合到SQL Server 2005中以逗号分隔的列表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文