考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
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
其他回答
在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
如果您使用的是SQL Server 2017或Azure,请参阅Mathieu Renda的回答。
当我试图连接两个具有一对多关系的表时,我也遇到了类似的问题。在SQL2005中,我发现XMLPATH方法可以非常容易地处理行的连接。
如果有一个名为STUDENTS的表
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
我期望的结果是:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
我使用了以下T-SQL:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
如果您可以在开头插入逗号并使用子字符串跳过第一个逗号,那么您可以以更紧凑的方式执行相同的操作,这样就不需要执行子查询:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
下面是一个使用“基本循环”和“rownum”实现给定场景的简单PL/SQL过程
表定义
CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;
让我们将值插入此表
INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');
程序从这里开始
DECLARE
MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);
BEGIN
SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;
LOOP
SELECT NAME INTO C_NAME FROM
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;
NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;
END LOOP;
dbms_output.put_line(SUBSTR(NSTR,2));
END;
后果
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函数
首先,您应该声明一个表变量并用表数据填充它,然后,使用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