转载 id value ----------- ---------- 1 cool 1 nice 1 wow 2 cool 2 wow 3 cool 3 nice 4 nice This is a result: id value ----------- ------------- 1 cool,nice,wow, 2 cool,wow, 3 cool,nice, 4 nice,实现方法: set nocount on declare @YourResultTable table (id int, 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(3, 'cool') insert @YourResultTable values(3, 'nice') insert @YourResultTable values(4, 'nice') select * from @YourResultTable declare @z varchar(100), @Step int, @q int select @Step = 0, @q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int) insert @ProcessTable select id, value, 0 from @YourResultTable order by id, valueupdate @ProcessTable set @z = value = case @q when id then @z else '' end + value + ',' , @q = @q + case @q when id then 0 else 1 end, @Step = ProcessStep = @Step + 1 from @ProcessTableselect id, value from @ProcessTable t join (select max(ProcessStep) MaxStep from @ProcessTable group by id) x on ProcessStep = MaxStepset nocount off
select identity(int,1,1) as id , * into #T from yourtable select depname, space(20) as EmpName into #T1 from yourtable group by DepName update #T1 set EmpName = '' declare @i int set @i = 1 while exists (select * from #T where id = @i) begin update #T1 set EmpName = A.EmpName + B.EmpName + ',' from #T1 as A, #T as B where A.DepName = B.DepName and B.id = @i set @i = @i + 1 end update #T1 set EmpName = left(EmpName, len(EmpName) - 1) from #T1 where len(EmpName) > 0 select * from #T1
id value
----------- ----------
1 cool
1 nice
1 wow
2 cool
2 wow
3 cool
3 nice
4 nice
This is a result:
id value
----------- -------------
1 cool,nice,wow,
2 cool,wow,
3 cool,nice,
4 nice,实现方法:
set nocount on
declare @YourResultTable table (id int, 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(3, 'cool')
insert @YourResultTable values(3, 'nice')
insert @YourResultTable values(4, 'nice')
select * from @YourResultTable
declare @z varchar(100),
@Step int,
@q int
select @Step = 0,
@q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int)
insert @ProcessTable
select id, value, 0
from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' ,
@q = @q + case @q when id then 0 else 1 end,
@Step = ProcessStep = @Step + 1
from @ProcessTableselect id,
value
from @ProcessTable t
join (select max(ProcessStep) MaxStep
from @ProcessTable
group by id) x
on ProcessStep = MaxStepset nocount off
select depname, space(20) as EmpName into #T1 from yourtable group by DepName
update #T1 set EmpName = ''
declare @i int
set @i = 1
while exists (select * from #T where id = @i)
begin
update #T1 set EmpName = A.EmpName + B.EmpName + ','
from #T1 as A, #T as B where A.DepName = B.DepName and B.id = @i
set @i = @i + 1
end
update #T1 set EmpName = left(EmpName, len(EmpName) - 1)
from #T1 where len(EmpName) > 0
select * from #T1