如果#T1為實表(如fdetail)則可以用用FUNCTION解決CREATE FUNCTION getItem
(@f1 varchar(15),@f2 varchar(15),@f3 varchar(15))
RETURNS varchar(500) AS
BEGIN declare @all varchar(500)
declare @top varchar(15)
declare @t table (Fitem varchar(15))
set @all=''
insert into @t
select fitem from fdetail where f1=@f1 and f2=@f2 and f3 = @f3while (exists(select fitem from @t))
begin
select top 1 @top= fitem from @t
set @all=@top+';'
delete @t where fitem = @top
endreturn @all
END
--然後:exec select f1,f2,f3,dbo.getitem(f1,f2,f3) from fdetail
(@f1 varchar(15),@f2 varchar(15),@f3 varchar(15))
RETURNS varchar(500) AS
BEGIN declare @all varchar(500)
declare @top varchar(15)
declare @t table (Fitem varchar(15))
set @all=''
insert into @t
select fitem from fdetail where f1=@f1 and f2=@f2 and f3 = @f3while (exists(select fitem from @t))
begin
select top 1 @top= fitem from @t
set @all=@top+';'
delete @t where fitem = @top
endreturn @all
END
--然後:exec select f1,f2,f3,dbo.getitem(f1,f2,f3) from fdetail
go
insert into t1 values('1','2','3','aa')
insert into t1 values('1','2','3','bb')
insert into t1 values('1','2','3','cc')
insert into t1 values('11','22','33','bb')
insert into t1 values('11','22','33','ee')
insert into t1 values('1','2','4','')
go
create function getstr(@l1 char(2),@l2 char(2),@l3 char(2))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+rtrim(l4) from t1
where l1=@l1 and l2=@l2 and l3=@l3
set @str=right(@str,len(@str)-1)
return @str
end
goselect l1,l2,l3,dbo.getstr(l1,l2,l3) from t1 group by l1,l2,l3
godrop function getstr
go
drop table t1
go
drop table temp1
GOdeclare @str varchar(1000)
set @str=''
declare @id1 int
declare @id2 int
declare @id3 int
declare MyCursor cursor for
select l1,l2,l3 from t1 group by l1,l2,l3create table temp1 (l1 int,l2 int,l3 int,l4 varchar(200))Open MyCursor
While (0=0) Begin
Fetch Next
From MyCursor
Into @id1,@id2,@id3
If (@@Fetch_Status<>0) Break
select @str=@str+case @str when '' then '' else ';' end +l4 from t1 where l1=@id1 and l2=@id2 and l3=@id3
insert into temp1 values(@id1,@id2,@id3,@str)
set @str=''
End
Close MyCursor
Deallocate MyCursorselect * from temp1
drop table temp1