--类似于sum(string)
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 @ProcessTable
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 @ProcessTable
(@id int)
RETURNS varchar(500)
as
begin
declare @all nvarchar(500)
set @all=''
select @all=@all+ fieldname + ';' from table where [id]=@fidreturn @all
ENDselect id,dbo.sumstring(id) from tablename group by id
sql语句的优势是:集合运算!你可以通过游标,循环等方式得到你的结果!
create table table1(tlid int,f1 varchar(20))
insert into table1 select 1,'i1'
union select 2,'i2'
create table A([ID] int,姓名 int,图书 varchar(20))
insert into A select 1, 1, '书'union select 2, 1, '书1' union select 3, 2, '书2' union select 4, 2, '书3'union select 5, 2, '书4' select * from A order by 姓名,图书create proc p_t1asbegindeclare @t int,@图书 varchar(20)--select @t=-1,@图书=''select 姓名,图书 as 总书目 into #t from A order by 姓名,图书 update #t set 总书目=@图书,@图书=case when 姓名=@t then @图书+','+总书目 else 总书目 end,@t=姓名
select 姓名,max(总书目) 总书目 from #t group by 姓名
endselect * from A order by 姓名,图书exec p_t1
或者用存储过程,
或者写个函数