连接行值 T-SQL
连接行值 T-SQL
我正在为一份报告整理一些数据,需要将其中一个表的行值连接起来。以下是基本的表结构:
Reviews
ReviewID ReviewDate
Reviewers
ReviewerID ReviewID UserID
Users
UserID FName LName
这是一个多对多的关系。每个Review可以有多个Reviewers;每个User可以与多个Reviews相关联。
基本上,我只想看到Reviews.ReviewID,Reviews.ReviewDate和与该Review相关的所有User的FName的连接字符串(逗号分隔)。
不要显示:
ReviewID---ReviewDate---User 1----------12/1/2009----Bob 1----------12/1/2009----Joe 1----------12/1/2009----Frank 2----------12/9/2009----Sue 2----------12/9/2009----Alice
显示为:
ReviewID---ReviewDate----Users 1----------12/1/2009-----Bob, Joe, Frank 2----------12/9/2009-----Sue, Alice
我找到了这篇文章,描述了一些实现方式,但其中大多数似乎只涉及一个表,而不是多个表;不幸的是,我对SQL不够精通,无法将其适应到我的情况。我特别感兴趣的是那篇文章中使用FOR XML PATH()的示例,因为它看起来最简洁和直观。
SELECT p1.CategoryId, ( SELECT ProductName + ', ' FROM Northwind.dbo.Products p2 WHERE p2.CategoryId = p1.CategoryId ORDER BY ProductName FOR XML PATH('') ) AS Products FROM Northwind.dbo.Products p1 GROUP BY CategoryId;
有人可以帮助我吗?非常感谢!
问题出现的原因是需要将表中某一列的所有值拼接成一个字符串。原始的解决方法是使用用户定义函数(UDF),但是这种方法比较繁琐。文章中提供了一种更简单的解决方法,使用T-SQL语言中的字符串函数和XML PATH语法来拼接行值。
解决方法如下:
使用以下代码来实现行值拼接:
select replace(replace(replace((cast(( select distinct columnName as X from tableName for xml path('')) as varchar(max))), '</X><X>', ', '),'<X>', ''),'</X>','')
这种方法被作者称为是最干净、不需要UDF的方法。而且在作者的案例中执行速度也非常快。读者对这种方法给予了高度的评价,称其为"纯金"。
在T-SQL中,有时候我们需要拼接表中某一列的所有值,这时候可以使用字符串函数和XML PATH语法来实现。这种方法不需要使用UDF,代码简洁,执行速度快。通过这种方法,我们可以轻松地将行值拼接成一个字符串。
问题原因:该问题的出现是因为在T-SQL中,没有直接的方法将多行数据的值连接成一个字符串。
解决方法:通过使用COALESCE函数和拼接操作符将多行数据的值连接成一个字符串。
以下是解决方法的示例代码:
declare @result varchar(1000) select @result = COALESCE(@result + ',' + A.col1, A.col1) FROM ( select col1 from [table] ) A select @result
这段代码将返回连接后的字符串,例如"value1,value2,value3,value4"。
然而,需要注意的是,这种方法不被Microsoft支持,并且可能会导致意外的结果。有人在simple-talk.com和marc.durdin.net的博客中对此进行了更详细的讨论。
问题的原因是在SQL查询中使用了FOR XML PATH('')来拼接行值,但是没有正确处理XML特殊字符如>
和&
。这导致在结果集中,如果数据包含Frank & Bill
,那么会得到Frank & Bill
。解决方法是使用一种聪明的方式来处理这个问题,可以参考stackoverflow.com/questions/5031204/…中的内容。