T-SQL对字符串的处理能力比较弱，比如我要循环遍历象1,2,3,4,5这样的字符串，如果用数组的话，遍历很简单，
但是T-SQL不支持数组，所以处理下来比较麻烦。下边的函数，实现了象数组一样去处理字符串。用临时表作为数组：
ALTER function [dbo].[F_Limitsplit](@IDs varchar(max),@UserID int)
returns @t table(UserID int,ID int)
as
begin
while(charindex(',',@IDs)<>0)
begin
insert @t(UserID,ID) values (@UserID,substring(@IDs,1,charindex(',',@IDs)-1))
set @IDs = stuff(@IDs,1,charindex(',',@IDs),'')
end
insert @t(UserID,ID) values (@UserID,@IDs)
return
end
-----------执行
select * from dbo.F_Limitsplit('1,2,3,4,5,6,7',1)
--------------------执行结果
UserID ID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
=====================================================
数据
id data
1 a
1 b
1 c
2 aa
2 bb
结果:
1 abc
2 aabb
就是要把data 列多行合并成一行显示
要求：一句sql语句. 不能用sp.
-----------------------------
SQL codecreate table tb(id int, data varchar(10))
insert into tb values(1 , 'a')
insert into tb values(1 , 'b')
insert into tb values(1 , 'c')
insert into tb values(2 , 'aa')
insert into tb values(2 , 'bb')
go
select id, [data]=replace((select ','+[data] from tb t where id=tb.id for xml path('')),',','')
from tb
group by id
drop table tb
--------------------------------------------
SELECT O.*,
ItemName=(select ProductName+',' from Bms_OrderGoodsDetail OGD
left join Bms_Products P on OGD.ProductID=P.ProductID
where OGD.OrderGoodsID= O.OrderGoodsID for xml path(''))
FROM [dbo].[Bms_OrderGoods] O
