这样的问题怎样解决

Question

表结构
  编号    身份证        职位
  001     123456        经理
  002     234567        会计师
  003     123456        律师
  004     123456        XXX
现在要求把身份证相同的记录删除掉,但是保留一条,而职称合并
结果
  编号   身份证        职称
  001    123456        经理,律师,XXX这样的假如用存储过程或其它怎样写???

Answer 1

参考一下，你可以把数据先移动到另外一个表中。create table t(id int,name varchar(2))
insert into t select 001,'aa'
insert into t select 001,'bb'
insert into t select 002,'cc'
GO
create function f_str(@id int)
returns varchar(1000)
as
begin
   declare @ret varchar(1000)
   set @ret=''
   select @ret=@ret+'.'+name from t where id=@id
   set @ret=stuff(@ret,1,1,'')
   return @ret
end
GO
select distinct name=dbo.f_str(id) from t
godrop table t
drop function dbo.f_str/*
name                                                                                                                                                                                                                                                             
---------------------------------------------------- 
aa.bb
cc
*/

Answer 2

create table t 
(    身份証 varchar(100),        職位 varchar(100)) 
insert into t
select       '123456',        '經理' union all
select       '234567',        '會計師' union all
select       '123456',        '律師' union all
select       '123456',        '醫生' union all
select       '234567',        '作家' alter function dbo.aa(@str varchar(10)) 
returns varchar(100)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+','+職位 from t where 身份証=@str
return stuff(@i,1,1,'')
endselect 編號=count(*),身份証,dbo.aa(身份証) from t
group by 身份証
編號          身份証                                                                                                                                                                                                       
----------- ----------------------------------------------------------------------------------------------------
3           123456                                                                                               經理,律師,醫生
2           234567                                                                                               會計師,作家(2 row(s) affected)