1,建自定义函数
create function getstr(@id Nchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(xmmc)+N',' from table
where xh=@id
if @str<>N''
set @str=left(@str,len(@str)-1)
return @str
endGO2,
select xh,dbo. getstr( xh) as xmmc from table group by xh
create function getstr(@id Nchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(xmmc)+N',' from table
where xh=@id
if @str<>N''
set @str=left(@str,len(@str)-1)
return @str
endGO2,
select xh,dbo. getstr( xh) as xmmc from table group by xh
没有简单点的吗?
----------- ----------
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