考虑一个包含姓名的数据库表格,有三行:
Peter
Paul
Mary
有没有简单的方法将这些内容转换成一个字符串:
Peter, Paul, Mary
?考虑一个包含姓名的数据库表格,有三行:
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
ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'
其中的“300”可以是任何宽度,考虑到您认为会显示的最大项目数量。
create table name
(first_name varchar2(30));
insert into name values ('Peter');
insert into name values ('Paul');
insert into name values ('Mary');
解决方案1:
select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
o/p=> Peter,Paul,Mary
select rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
o/p=> Peter,Paul,Mary
declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''
DECLARE @MyTable TABLE
(
Id int identity,
Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)
while @i < @rowsCount
begin
set @names = @names + ', ' + (select name from @MyTable where Id = @i)
set @i = @i + 1
end
select @names
这个例子是在 SQL Server 2008 R2 版本下测试的。
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
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
SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM Names;
ConcatenatedNames
-----------------
Peter, Paul, Mary
declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone