题目描述:按照id:vertex格式把所有行记录按照id从小到大合并到一行记录中。
数据如下表:
预期结果如下图:
解决方案:
方案一:基于游标
declare t_cur cursor
for select id,vertex from temp order by id;
declare @result varchar(max);
declare @id int;
declare @vertex varchar(10);
set @result = '';
open t_cur;
fetch t_cur into @id,@vertex;
while(@@fetch_status = 0)
begin
set @result = @result + cast(@id as varchar) + ':' + isnull(@vertex,'') + ',';
fetch t_cur into @id,@vertex;
end
close t_cur;
deallocate t_cur;
方案二:一条SQL语句declare @result varchar(max)
set @result = ''
select @result = @result + cast(id as varchar) + ':'+ isnull(vertex,'') + ','
from temp
order by id
--delete the last comma
set @result = substring(@result,1,len(@result)-1)
select @result
结论:
显然第二个解决方案的效率比第一个要高,但是就解决问题而言,如果能想到好的解决方案固然很好,如果想不到好的解决方案,游标至少是一种可行的解决方案。
If you can't find a good solution, then you should hug your "bad" one.
参考: