如何在SQL Server中将多行合并为一列？

Question

如何在SQL Server中将多行合并为一列？

4

我已经搜索了很多，但是我无法找到答案。我相对较新于SQL Server，语法还不太熟悉。我有这个数据结构（简化版）：

我想要一种方法来获取所有照片信息（容易实现），并将该照片的所有标签连接起来，例如CONCAT(username, ', ') AS Tags，当然需要删除最后一个逗号。我试图使用这篇文章中的方法，但是当我尝试运行查询时会出现错误，说我不能使用DECLARE语句...你们有什么想法吗？我正在使用VS08和其中安装的任何DB（我通常使用MySQL，所以我不知道这是哪种DB...它是一个.mdf文件？）

- Jason

3个回答

3

我会创建一个UDF：

create function GetTags(PhotoID int) returns @tags varchar(max)
as
begin
    declare @mytags varchar(max)
    set @mytags = ''

    select @mytags = @mytags + ', ' + tag from tags where photoid = @photoid

    return substring(@mytags, 3, 8000)
end

然后，你需要做的就是：

select GetTags(photoID) as tagList from photos

- Eric

这是一个“内联”，“表值”还是“标量值”函数？这些是 Visual Studio 给我的选项... - Jason

标量值 - 抱歉我没有具体说明。 - Eric

稍微解释一下：表值函数和内联函数都返回表类型。这些选项可以帮助您处理某些语法，但如果您直接运行此SQL，则它将自动成为标量。 - Eric

@eric - 非常感谢你...稍微修改一下，我就让它工作了！现在作为奖励，我该如何删除尾随的逗号（我将顺序改变了一下，使得用户名首先出现，然后是@mytags，否则会有一个前导逗号）？ - Jason

@Jason：抱歉，在泛化函数时我忘记了 substring。我先在自己的机器上测试过了。这将删除前导逗号（比删除尾随逗号更快）。 - Eric

0

Street_Name   ; Street_Code

西 | 14

东 | 7

西+东 | 714

如果想要显示两个不同行连接在一起，应该如何做？（我的意思是我想要从选择结果中显示最后一行。我的表格有第一条和第二条记录）

- Ilaria

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rob Farley · Accepted Answer

好的，我感觉需要介入评论一下如何在SQL Server中将多个行连接成一列？并提供更受欢迎的答案。

很抱歉，使用标量值函数会影响性能。只需打开SQL Profiler并查看在使用调用表的标量函数时发生了什么。

此外，不建议使用“更新变量”技术进行连接，因为该功能可能在未来版本中无法继续使用。

做字符串连接的首选方法是使用FOR XML PATH。

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

为了演示 FOR XML PATH 的用法，请考虑以下情况，假设您有一个包含两个字段“id”和“name”的表

SELECT id, name
FROM table
order by name
FOR XML PATH('item'),root('itemlist')
;

给予：

<itemlist><item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item></itemlist>

但是如果省略ROOT，则会得到略微不同的结果：

SELECT id, name
FROM table
order by name
FOR XML PATH('item')
;

<item><id>2</id><name>Aardvark</a></item><item><id>1</id><name>Zebra</name></item>

如果您将空的PATH字符串放入其中，您甚至可以更接近普通的字符串连接：

SELECT id, name
FROM table
order by name
FOR XML PATH('')
;

<id>2</id><name>Aardvark</a><id>1</id><name>Zebra</name>

现在进入真正棘手的部分...如果你以@符号开头命名列，它就变成了属性，如果一个列没有名称（或者你称之为[*]），那么它也会省略该标签：

SELECT ',' + name
FROM table
order by name
FOR XML PATH('')
;

,Aardvark,Zebra

现在最后，要去掉前导逗号，可以使用STUFF命令。STUFF(s,x,n,s2)从位置x开始提取s中的n个字符。然后用s2替换它们。所以：

SELECT STUFF('abcde',2,3,'123456');

结果为：

a123456e 现在请查看上面针对标签列表的查询。

select
 stuff((select ', ' + t.tag from tags t where t.photoid = p.photoid order by tag for xml path('')),1,2,'') as taglist
 ,*
from photos
order by photoid;

对于每张照片，我都有一个子查询来获取标签并将它们按顺序与逗号和空格连接起来。然后我将该子查询放入stuff命令中以去除前导逗号和空格。

抱歉如果有任何拼写错误 - 我实际上还没有在我的机器上创建表来测试这个。