--原始數據 _id _type _branch 1 B 6110 1 B 6111 1 B 6112 1 C AROFAS 1 C AROMXX 2 B 3612 2 B 3212 2 C CHIFAS--結果 _id _type _branch 1 B 6110,6111,6112, 1 C AROFAS,AROMXX, 2 B 3612,3212, 2 C CHIFAS,
select _id, _type, _branch=stuff((select ','+_branch from tb where _id=t._id and _type=t._type for xml path('')),1,1,'') from tb t group by _id,_type
select [_id],[_type], stuff((select id from tb where [_id]=t.[_id] for xml),1,1) as [_branch] from tb t
select [_id], [_type], stuff((select ','+_branch from tb where [_id]=t.[_id] and [_type]=t.[_type] for xml path('')),1,1,'') as [_branch] from tb t group by [_id],[_type]
create function f_str(@id int,@type varchar(10)) returns varchar(100) as begin declare @sql varchar(100) set @sql='' select @sql=','+@sql+ _branch from tb where _id=@id and @type=_type
return stuff(@sql,1,1,'') endselect _id,_type,dbo.f_str( _id,_type) from tb group by _id,_type
create function f_str(@id int,@type varchar(10)) returns varchar(100) as begin declare @sql varchar(100) set @sql='' select @sql=@sql+ _branch+',' from tb where _id=@id and @type=_type
return @sql endselect _id,_type,dbo.f_str( _id,_type) from tb group by _id,_type
SELECT _id, _type, _branch = STUFF((SELECT _branch+',' FROM tb WHERE _id=t._id AND _type=t._type FOR XML PATH('')), 1,1,'') FROM tb t GROUP BY _id, _type
SELECT _id, _type, _branch = RTRIM((SELECT _branch+',' FROM tb WHERE _id=t._id AND _type=t._type FOR XML PATH(''))) FROM tb t GROUP BY _id, _type
不明白爲什麽要用到for xml,我在去掉for xml()后也是運行無效。 SELECT notice_ID, btype, branch = RTRIM((SELECT branch+',' FROM webnotice_detail_branch WHERE notice_ID=t.notice_ID AND notice_ID=t.notice_ID)) FROM webnotice_detail_branch t GROUP BY notice_ID, btype Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
執行帶有for xml('')語句后 Line 3: Incorrect syntax near 'xml'.
create table #tb (_id int, _type nvarchar(5), _branch nvarchar(20)) insert #tb select 1 ,'B','6110' union all select 1 ,'B','6111' union all select 1 ,'B','6112' union all select 1 ,'C','AROFAS' union all select 1 ,'C','AROMXX' union all select 2 ,'B','3612' union all select 2 ,'B','3212' union all select 2 ,'C','CHIFAS'select distinct _id,_type, _branch=stuff((select ','+_branch from #tb as it where it._id=t._id and it._type=t._type for xml path('')),1,1,'') from #tb as t凑热闹~~
_branch=stuff((select ','+_branch from tb where _id=t._id and _type=t._type for xml path('')),1,1,'')
from tb t
group by _id,_type
[_id],[_type],
stuff((select id from tb where [_id]=t.[_id] for xml),1,1) as [_branch]
from
tb t
[_id], [_type],
stuff((select ','+_branch from tb where [_id]=t.[_id] and [_type]=t.[_type] for xml path('')),1,1,'') as [_branch]
from
tb t
group by
[_id],[_type]
as
begin
declare @sql varchar(100) set @sql='' select @sql=','+@sql+ _branch
from tb
where _id=@id and @type=_type
return stuff(@sql,1,1,'')
endselect _id,_type,dbo.f_str( _id,_type) from tb group by _id,_type
create function f_str(@id int,@type varchar(10)) returns varchar(100)
as
begin
declare @sql varchar(100) set @sql='' select @sql=@sql+ _branch+','
from tb
where _id=@id and @type=_type
return @sql
endselect _id,_type,dbo.f_str( _id,_type) from tb group by _id,_type
_id,
_type,
_branch = STUFF((SELECT _branch+',' FROM tb WHERE _id=t._id AND _type=t._type FOR XML PATH('')), 1,1,'')
FROM tb t
GROUP BY _id, _type
_id,
_type,
_branch = RTRIM((SELECT _branch+',' FROM tb WHERE _id=t._id AND _type=t._type FOR XML PATH('')))
FROM tb t
GROUP BY _id, _type
SELECT
notice_ID,
btype,
branch = RTRIM((SELECT branch+',' FROM webnotice_detail_branch WHERE notice_ID=t.notice_ID AND notice_ID=t.notice_ID))
FROM webnotice_detail_branch t
GROUP BY notice_ID, btype
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Line 3: Incorrect syntax near 'xml'.
create table #tb
(_id int, _type nvarchar(5), _branch nvarchar(20))
insert #tb
select 1 ,'B','6110' union all
select 1 ,'B','6111' union all
select 1 ,'B','6112' union all
select 1 ,'C','AROFAS' union all
select 1 ,'C','AROMXX' union all
select 2 ,'B','3612' union all
select 2 ,'B','3212' union all
select 2 ,'C','CHIFAS'select distinct _id,_type,
_branch=stuff((select ','+_branch from #tb as it where it._id=t._id and it._type=t._type for xml path('')),1,1,'')
from #tb as t凑热闹~~