可能是重复问题:
在SQL Server中将字段值连接为字符串的方法
在SQL Server中,什么是WM_CONCAT的等效函数?
你没有相应的函数,但你仍然可以模拟(使用CROSS APPLY
和 FOR XML PATH('')
)。例如:
USERID ADDRESSLINE1
==========================
1 First Street
1 Second Street
2 32th Street
2 24th Street
2 25th Street
将会产生结果
USERID ADDRESSLIST
============================
1 First Street, Second Street
2 32th Street, 24th Street, 25th Street
使用以下查询:
SELECT a.UserID,
SUBSTRING(d.Addresses,1, LEN(d.Addresses) - 1) AddressList
FROM
(
SELECT DISTINCT UserID
FROM tableName
) a
CROSS APPLY
(
SELECT [AddressLine1] + ', '
FROM tableName AS B
WHERE A.UserID = B.UserID
FOR XML PATH('')
) D (Addresses)
SELECT STUFF( (SELECT ',' + CAST(field_name AS VARCHAR(MAX)) FROM table_name FOR XML PATH('')), 1, 1, '' ) AS concatenated_string;
其中,field_name 替换为要连接的字段名,table_name 替换为表名,concatenated_string 是最终生成的字符串列的别名。 - Habib