create function getstr(@id Nchar(10))
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(c2)+N',' from T
where c1=@id
set @str=left(@str,len(@str)-1)
return @str
end
GO调用:
select c1,dbo.getstr(c1) as other from t
returns Nvarchar(2000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(c2)+N',' from T
where c1=@id
set @str=left(@str,len(@str)-1)
return @str
end
GO调用:
select c1,dbo.getstr(c1) as other from t
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
select @result=@result+c2 from yourtable where c1=@c2
return @result
end
then
select c1,getresult(c1) from yourtable
set nocount on
declare @YourResultTable table (id varchar(2), value varchar(10))
insert @YourResultTable values('1', 'cool')
insert @YourResultTable values('1', 'nice')
insert @YourResultTable values('1', 'wow')
insert @YourResultTable values('2', 'cool')
insert @YourResultTable values('2', 'wow')
insert @YourResultTable values('4', 'nice')
insert @YourResultTable values('6', 'cool')
insert @YourResultTable values('6', 'nice')--select * from @YourResultTabledeclare @z varchar(100),@q varchar(2)
select @q = nulldeclare @ProcessTable table (id varchar(2), value varchar(100))
insert @ProcessTable
select id, value from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +',' , @q = id
from @ProcessTable
--select * from @ProcessTableselect id,max(value) from @ProcessTable group by idset nocount off
因为sqlserver不支持函数,所以想知道sqlserver是如何实现的,真的感谢大家。