考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
首先,您应该声明一个表变量并用表数据填充它,然后,使用WHILE循环,逐个选择行并将其值添加到nvarchar(max)变量中。
Go
declare @temp table(
title nvarchar(50)
)
insert into @temp(title)
select p.Title from dbo.person p
--
declare @mainString nvarchar(max)
set @mainString = '';
--
while ((select count(*) from @temp) != 0)
begin
declare @itemTitle nvarchar(50)
set @itemTitle = (select top(1) t.Title from @temp t)
if @mainString = ''
begin
set @mainString = @itemTitle
end
else
begin
set @mainString = concat(@mainString,',',@itemTitle)
end
delete top(1) from @temp
end
print @mainString
其他回答
此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的For XML PATH方法之一。
使用COALENCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
只是一些解释(因为这个答案似乎得到了相对规律的观点):
联合实际上只是一种有助于实现两件事的欺骗:
1) 无需使用空字符串值初始化@Names。
2) 无需在末端去除额外的分隔符。
如果一行具有NULL Name值(如果存在NULL,NULL将使该行之后的@Names为NULL,而下一行将再次以空字符串开始),则上述解决方案将给出错误的结果。使用以下两种解决方案之一即可轻松解决:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
取决于您想要的行为(第一个选项只是过滤掉NULL,第二个选项用标记消息将它们保留在列表中[用适合您的内容替换“N/a”])。
使用XML帮助我用逗号分隔行。对于额外的逗号,我们可以使用SQL Server的replace函数。使用AS“data()”将用空格连接行,而不是添加逗号,稍后可以用逗号替换,语法如下所示。
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
在Oracle中,它是wm_concat。我相信这个功能在10g及更高版本中可用。
我在家里无法访问SQL Server,所以我猜测这里的语法,但大致上是这样的:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
提出了递归CTE解决方案,但没有提供代码。下面的代码是递归CTE的示例。
请注意,虽然结果与问题相符,但数据与给定的描述并不完全相符,因为我假设您确实希望对行组(而不是表中的所有行)执行此操作。将其更改为与表中的所有行相匹配是读者的练习。
;WITH basetable AS (
SELECT
id,
CAST(name AS VARCHAR(MAX)) name,
ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
COUNT(*) OVER (Partition BY id) recs
FROM (VALUES
(1, 'Johnny', 1),
(1, 'M', 2),
(2, 'Bill', 1),
(2, 'S.', 4),
(2, 'Preston', 5),
(2, 'Esq.', 6),
(3, 'Ted', 1),
(3, 'Theodore', 2),
(3, 'Logan', 3),
(4, 'Peter', 1),
(4, 'Paul', 2),
(4, 'Mary', 3)
) g (id, name, seq)
),
rCTE AS (
SELECT recs, id, name, rw
FROM basetable
WHERE rw = 1
UNION ALL
SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
FROM basetable b
INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
OPTION (MAXRECURSION 101)