考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
我真的很喜欢Dana回答的优雅,只想让它变得完整。
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
其他回答
在SQLServer2005及更高版本中,使用下面的查询连接行。
DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t ) t
此答案需要服务器上的某些权限才能工作。
装配是一个很好的选择。有很多网站解释如何创建它。我认为解释得很好的就是这个。
如果您愿意,我已经创建了程序集,可以在这里下载DLL文件。
下载后,需要在SQL Server中运行以下脚本:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;
CREATE Assembly concat_assembly
AUTHORIZATION dbo
FROM '<PATH TO Concat.dll IN SERVER>'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.concat (
@Value NVARCHAR(MAX)
, @Delimiter NVARCHAR(4000)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO
sp_configure 'clr enabled', 1;
RECONFIGURE
请注意,服务器可以访问程序集的路径。由于您已成功完成所有步骤,因此可以使用以下功能:
SELECT dbo.Concat(field1, ',')
FROM Table1
自SQL Server 2017以来,可以使用STRING_AGG函数。
我并没有做过任何关于性能的分析,因为我的列表中只有不到10项,但在查看了30多个答案后,我感到很惊讶,我仍然对已经给出的类似答案进行了修改,类似于对单个组列表使用COALESCE,甚至不必设置我的变量(无论如何默认为NULL),并且它假设我的源数据表中的所有条目都是非空的:
DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData
我确信COALENCE内部使用了相同的想法。让我们希望微软不会在我身上改变这一点。
此方法仅适用于Teradata Aster数据库,因为它使用NPATH函数。
再次,我们有桌上学生
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
然后使用NPATH,只需一次SELECT:
SELECT * FROM npath(
ON Students
PARTITION BY SubjectID
ORDER BY StudentName
MODE(nonoverlapping)
PATTERN('A*')
SYMBOLS(
'true' as A
)
RESULT(
FIRST(SubjectID of A) as SubjectID,
ACCUMULATE(StudentName of A) as StudentName
)
);
结果:
SubjectID StudentName
---------- -------------
1 [John, Mary, Sam]
2 [Alaina, Edward]
我们可以使用RECUSRSIVITY、WITH CTE、union ALL,如下所示
declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')
declare @myresult as table(id int,str nvarchar(max),ind int, R# int)
;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte
select top 1 str from @myresult order by R# desc