create function getstr(@content char(20))
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+职务 from 表
where content=@content
set @str=right(@str,len(@str)-1)
return @str
end
go语句:
select distinct id,dbo.getstr(id) from 表
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+职务 from 表
where content=@content
set @str=right(@str,len(@str)-1)
return @str
end
go语句:
select distinct id,dbo.getstr(id) from 表
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+职务 from 纪录人的职务的表
where content=@content
set @str=right(@str,len(@str)-1)
return @str
end
go语句:
select distinct id,dbo.getstr(id) 职务 from 纪录人的职务的表
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
合并用户在某个部门的职务呢?比如,部门职务表user_id, depart_id, role
1, 1, '董事长'
1, 1, '助手'
1, 2, '总经理'
要的结果表是:
1, 1, '董事长,助手'
1, 2, '总经理'好像用case的话,就不好case两个东西了吧?
declare @YourResultTable table (id1 int, id2 int, value varchar(10))
insert @YourResultTable values(1,1,'')
insert @YourResultTable values(1,1,'nice')
insert @YourResultTable values(1,2,'wow')
insert @YourResultTable values(2,1,'cool')
insert @YourResultTable values(2,1,'wow')
insert @YourResultTable values(4,1,'nice')
insert @YourResultTable values(6,1,'cool')
insert @YourResultTable values(6,1,'nice')select * from @YourResultTabledeclare @z varchar(100),@id1 int, @id2 int
select @id1 = nullupdate @YourResultTable
set @z = value = case when (@id1=id1 and @id2=id2) then @z else '' end + value + case value when '' then '' else ',' end, @id1 = id1, @id2=id2
from @YourResultTabledelete @YourResultTable from @YourResultTable a where a.value<>
(select max(value) from @YourResultTable b where b.id1=a.id1 and b.id2=a.id2)select * from @YourResultTableset nocount off