考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
这也很有用
create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test
回报
Peter,Paul,Mary
其他回答
在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语法
即
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
结果会变成
Id Emails
1 abc@gmail.com
2 NULL
3 def@gmail.com, xyz@gmail.com
即使您的数据包含无效的XML字符,这也会起作用
“”},{“_”:“”是安全的,因为如果您的数据包含“”},{”_“:“”,它将被转义为“},{\”_\“:\”
可以用任何字符串分隔符替换“,”
在SQL Server 2017中,Azure SQL数据库
您可以使用新的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内部使用了相同的想法。让我们希望微软不会在我身上改变这一点。
以下是实现这一目标的完整解决方案:
-- Table Creation
CREATE TABLE Tbl
( CustomerCode VARCHAR(50)
, CustomerName VARCHAR(50)
, Type VARCHAR(50)
,Items VARCHAR(50)
)
insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'
-- function creation
GO
CREATE FUNCTION [dbo].[fn_GetItemsByType]
(
@CustomerCode VARCHAR(50)
,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE ( Items VARCHAR(5000) )
AS
BEGIN
INSERT INTO @ItemType(Items)
SELECT STUFF((SELECT distinct ',' + [Items]
FROM Tbl
WHERE CustomerCode = @CustomerCode
AND Type=@Type
FOR XML PATH(''))
,1,1,'') as Items
RETURN
END
GO
-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type)
from Tbl
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
from
(
select
distinct CustomerCode
,CustomerName
,Type
,F.Items
FROM Tbl T
CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
) x
pivot
(
max(Items)
for Type in (' + @cols + ')
) p '
execute(@query)
我们可以使用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
在SQL Server vNext中,这将内置STRING_AGG函数。在STRING_AGG(Transact-SQL)中了解有关它的更多信息。