考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
我们可以使用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 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,所以我猜测这里的语法,但大致上是这样的:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
在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函数
使用XML帮助我用逗号分隔行。对于额外的逗号,我们可以使用SQL Server的replace函数。使用AS“data()”将用空格连接行,而不是添加逗号,稍后可以用逗号替换,语法如下所示。
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')