怎么实现数据表中记录的合并

--> 测试数据: [tb]if object_id('[tb]') is not null drop table [tb]create table [tb] (职务号 int,文书号 varchar(6),权限 varchar(5),公司 varchar(2))insert into [tb]select 1,'010104','add','01' union allselect 1,'010104','del','01' union allselect 1,'010104','print','01'--开始查询select 职务号,文书号,增加=MAX(case 权限 when 'add' then 1 else 0 end),删除=MAX(case 权限 when 'del' then 1 else 0 end),打印=MAX(case 权限 when 'print' then 1 else 0 end),公司 from tbgroup by 职务号,文书号,公司--结束查询drop table [tb]/*职务号         文书号    增加          删除          打印          公司----------- ------ ----------- ----------- ----------- ----1           010104 1           1           1           01(1 行受影响)
------解决方案--------------------
SQL code
select  职务号,文书号,max(case when 权限='add' then 1 else 0 end) as 增加,max(case when 权限='del' then 1 else 0 end) as 删除,max(case when 权限='print' then 1 else 0 end) as 打印,公司from a  group by 职务号,文书号,公司
------解决方案--------------------
SQL code
select  职务号,文书号,  max(case when 权限='add' then 1 else 0 end) as 增加,  max(case when 权限='del' then 1 else 0 end) as 删除,  max(case when 权限='print' then 1 else 0 end) as 打印,公司from a  group by  职务号,文书号,公司
------解决方案--------------------
SQL code
--  动态:declare @sql varchar(8000)set @sql = 'select   职务号,文书号,公司 'select @sql = @sql + ' , sum(case 权限 when ''' + 权限 + ''' then 1 else 0 end) [' + 权限 + ']'from (select distinct 权限 from tb) as aset @sql = @sql + ' from tb group by  职务号,文书号,公司'exec(@sql)
------解决方案--------------------
SQL code
create table #t1(id int,code varchar(50),rightname varchar(50),company varchar(50))insert into #t1select 1 as id,'010104' as code,'add' as rightname,'01' as company union allselect 1,'010104','del','01' union allselect 1,'010104','print','01'--==================================================select id ,code,company,max(case rightname when 'add' then '1' else '' end) as 新增 ,max(case rightname when 'del' then '1' else '' end) as 删除 ,max(case rightname when 'print' then '1' else '' end) as 打印 from #t1group by  id ,code,company    drop table #t1-----------------------(3 行受影响)id          code                                               company                                            新增   删除   打印----------- -------------------------------------------------- -------------------------------------------------- ---- ---- ----1           010104                                             01                                                 1    1    1(1 行受影响)