考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
在Oracle中,它是wm_concat。我相信这个功能在10g及更高版本中可用。
其他回答
在Chris Shaffer的回答之上:
如果您的数据可能会重复,例如
Tom
Ali
John
Ali
Tom
Mike
而不是汤姆、阿里、约翰、阿里、汤姆、迈克
您可以使用DISTINCT避免重复,并让Tom、Ali、John、Mike:
DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的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”])。
我并没有做过任何关于性能的分析,因为我的列表中只有不到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内部使用了相同的想法。让我们希望微软不会在我身上改变这一点。
使用递归查询,您可以执行以下操作:
-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;
-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');
-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank where rang=1
union all
select f0.*, cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1
)
select AllName from tmpRecursive
where rang=NbRow
SELECT PageContent = Stuff(
( SELECT PageContent
FROM dbo.InfoGuide
WHERE CategoryId = @CategoryId
AND SubCategoryId = @SubCategoryId
for xml path(''), type
).value('.[1]','nvarchar(max)'),
1, 1, '')
FROM dbo.InfoGuide info