为了说明,假设我有两个表,如下所示:
br />
VehicleID Name
1 Chuck
2 Larry
LocationID VehicleID City
1 1 New York
2 1 Seattle
3 1 Vancouver
4 2 Los Angeles
5 2 Houston
我知道可以使用服务器端游标来完成,即:这需要大量的代码。我想要的是一个通用函数,它允许我执行以下操作:
VehicleID Name Locations
1 Chuck New York, Seattle, Vancouver
2 Larry Los Angeles, Houston
这可能吗?还是类似的东西?
#1 楼
如果您使用的是SQL Server 2005,则可以使用FOR XML PATH命令。<br />SELECT [VehicleID]
, [Name]
, (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX))
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]
它比使用游标容易得多,并且看起来工作得相当好。
评论
这将可以很好地处理此数据,但是如果您的数据可能包含xml特殊字符(例如<,>和&),则将替换它们(<等)。
– Gilm
08-10-12在0:39
@James您可以使用CTE来完成此任务:使用MyCTE(VehicleId,名称,位置)AS(SELECT [VehicleID],[Name],(SELECT CAST(City +','AS VARCHAR(MAX))FROM [Location] XML路径(''))的AS位置(VehicleID = Vehicle.VehicleID))[车辆])AS位置
–芒
2011年5月24日15:27
我在修改此代码时变得有些困惑,所以现在发布了我自己的问题
–詹姆斯·帕里斯(James Parish)
11年5月24日在16:14
您可以将子查询包装在STUFF函数中,以消除逗号。只需使查询以“,”开头,然后将子查询包装在:STUFF(subquery,1,2,'')
– MickJuice
2013年12月20日4:06
可以这样插入换行符:“某些文本” + CHAR(13)+ CHAR(10)+“下一行文本”。
–thefellow3j
16年2月16日在17:20
#2 楼
请注意,Matt的代码将在字符串末尾产生一个逗号。如Lance的帖子中的链接所示,使用COALESCE(或就此而言为ISNULL)使用了类似的方法,但不会给您留下多余的逗号。为了完整起见,以下是sqlteam.com上Lance的链接中的相关代码:DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
评论
我认为,没有多余的逗号是很好的,但比起已接受的解决方案,它也更易于阅读和理解。非常感谢!
–贝斯卡
2012年10月25日19:47
这不是一个可靠的解决方案。
–LukášLánský
2014-2-26在18:42
@lukasLansky它的可靠性,只要您不关心订单
– codeulike
15年4月28日在21:19
即使您不在乎顺序,它也可能会跳过结果中的数据。
– Der_Meister
16-2-9的3:44
添加ORDER BY以获得可靠的订单?
–皮特·阿尔文
17年7月12日在14:20
#3 楼
我不相信有一种方法可以在一个查询中完成它,但是您可以使用一个临时变量来玩这样的技巧:在光标上,可能更有效。评论
我相当确定您可以将“可能”排除在最后一行之外。
– Marc Gravell♦
08-10-12在9:13
“我不相信有一种方法可以在一个查询中完成”。是的,确实存在。 SQL Server 2005同时具有FOR XML和CTE。
– T.J.拥挤者
15年5月23日在11:57
这不可靠,取决于执行计划,行可能会丢失。参见KB。
– Der_Meister
16-2-9在3:52
这种技术或功能称为什么?当进行SELECT @s = @s变量赋值时,包括其现有值,并为结果集中的每一行再次赋值吗?
–保达
16年5月16日在18:08
很好的答案,但是请注意,此解决方案似乎没有记录,因此,Microsoft将来有可能在没有警告的情况下删除执行此操作的功能。
– Pouria Moosavi
9月4日10:54
#4 楼
在单个SQL查询中,无需使用FOR XML子句。公共表表达式用于递归地连接结果。
评论
谢谢你这是不使用变量,函数,FOR XML子句或CLR代码的少数几个解决方案之一。这意味着我能够调整您的解决方案以解决TSQL初学者挑战4-将多个行中的值连接在一起。
–伊恩·塞缪尔·麦克莱恩·艾尔德(Iain Samuel McLean Elder)
2011年8月14日在16:20
谢谢!我必须将表示为单独的布尔短语行的一系列SQL代码片段转换为单个复杂的代码表达式,并且很高兴尝试您的方法。
–Paul Chernoch
13年2月18日在16:43
与其他解决方案相比,这是否具有性能优势?
– PeonProgrammer
2015年9月9日在22:10
@PeonProgrammer不,对于大型结果集,它的工作效果非常差,并且可能会给您带来错误,“在语句完成之前已耗尽了最大递归100。” (您可以通过在末尾指定OPTION(MAXRECURSION 0)来解决此问题,但是您的查询可能要花很长时间才能运行。
–柯克·沃尔(Kirk Woll)
16-3-25在17:10
#5 楼
从我所见,FOR XML
(如前所述)是执行操作的唯一方法,如果您还希望像OP一样选择其他列(我猜大多数人会这样做)。使用
COALESCE(@var...
不允许包含更新:
感谢programmingsolutions.net,有一种方法可以删除“尾随”逗号。
通过将其变为前导逗号并使用MSSQL的
STUFF
函数可以用空字符串替换第一个字符(前导逗号),如下所示:stuff(
(select ',' + Column
from Table
inner where inner.Id = outer.Id
for xml path('')
), 1,1,'') as Values
#6 楼
在SQL Server 2005中SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
在SQL Server 2016中
您可以使用FOR JSON语法
/>
ie
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
结果将变为br />
'“},{”“:”'是安全的,因为如果您的数据包含'“},{”“:”',它将被转义为“},{\” _ \“ :\“
您可以用任何字符串分隔符替换','
在SQL Server 2017中,Azure SQL数据库
您可以使用新的STRING_AGG函数
评论
我取出了这一块:TYPE).value('text()[1]','nvarchar(max)')仍然可以正常工作……不确定该怎么做。
–亚当·诺夫辛格(Adam Nofsinger)
2011年7月6日在20:43
supposed to decode the xml, if [City] had char like & < >, the output will become, & < > , if you sure [City] don't have those special chars, then it's safe to remove it. – Steven Chong
– Steven Chong
Jun 4 '12 at 9:44
+1。这个答案被低估了。您应该对其进行编辑,以提及这是不会逃脱&&>等特殊字符的唯一答案之一。此外,如果使用以下方法,结果也不会相同:.value('。','nvarchar(最高)')?
–保达
16年4月27日在19:27
嗨,鲍达德,结果是一样的,但是正如我测试的那样,当使用“ text()[1]”而不是“。”时,性能更好。
–史蒂文·冲(Steven Chong)
16年4月28日在1:44
#7 楼
以下代码适用于Sql Server 2000/2005/2008CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @csvCities VARCHAR(1000)
SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
FROM Vehicles
WHERE VehicleId = @VehicleId
return @csvCities
END
-- //Once the User defined function is created then run the below sql
SELECT VehicleID
, dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID
评论
VARCHAR(1000),这是某种限制,不是吗?因为当我在列列表上运行类似的串联查询时,无论指定的大小如何,它将停止约950个字符。
–约翰·莱德格伦(John Leidegren)
09年8月25日在7:29
#8 楼
我通过创建以下函数找到了解决方案:CREATE FUNCTION [dbo].[JoinTexts]
(
@delimiter VARCHAR(20) ,
@whereClause VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Texts VARCHAR(MAX)
SELECT @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto
FROM SomeTable AS T
WHERE T.SomeOtherColumn = @whereClause
RETURN @Texts
END
GO
用法:
SELECT dbo.JoinTexts(' , ', 'Y')
评论
这非常类似于Mike Powell和Binoj Antony的答案。
– Andriy M
2011年5月30日在18:22
出色的解决方案,因为可读性优于其他答案+1
– PeonProgrammer
2015年9月9日在22:09
#9 楼
Mun的答案对我不起作用,因此我对该答案进行了一些更改以使其起作用。希望对您有所帮助。使用SQL Server 2012:
SELECT [VehicleID]
, [Name]
, STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,City)
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '') AS Locations
FROM [Vehicle]
#10 楼
版本说明:对于此解决方案,您必须使用SQL Server 2005或更高版本且兼容性级别设置为90或更高。从表的列中获取的一组字符串值。参考示例1的C#版本:
change: this.intermediateResult.Append(value.Value).Append(',');
to: this.intermediateResult.Append(value.Value);
和
change: output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
to: output = this.intermediateResult.ToString();
使用自定义聚合时,您可以选择使用自己的定界符,也可以根本不使用定界符,例如:
SELECT dbo.CONCATENATE(column1 + '|') from table1
注意:您尝试汇总处理的数据。如果尝试连接成千上万的行或许多非常大的数据类型,则可能会收到.NET Framework错误,指出“缓冲区不足。”
#11 楼
对于其他答案,阅读答案的人员必须了解车辆表,并创建车辆表和数据以测试解决方案。下面是一个使用SQL Server“ Information_Schema.Columns”表的示例。通过使用此解决方案,无需创建表或添加数据。本示例为数据库中的所有表创建一个用逗号分隔的列名列表。
SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
#12 楼
试试这个查询SELECT v.VehicleId, v.Name, ll.LocationList
FROM Vehicles v
LEFT JOIN
(SELECT
DISTINCT
VehicleId,
REPLACE(
REPLACE(
REPLACE(
(
SELECT City as c
FROM Locations x
WHERE x.VehicleID = l.VehicleID FOR XML PATH('')
),
'</c><c>',', '
),
'<c>',''
),
'</c>', ''
) AS LocationList
FROM Locations l
) ll ON ll.VehicleId = v.VehicleId
#13 楼
如果您正在运行SQL Server 2005,则可以编写自定义CLR聚合函数来处理此问题。C#版本:
评论
具有更完整响应的类似答案stackoverflow.com/a/17591536/1587302