select 姓名, 用户ID, sum(case when 权限 = 'C1' then 1 else 0 end) as 权限c1, sum(case when 权限 = 'C2' then 1 else 0 end) as 权限c2, sum(case when 权限 = 'C3' then 1 else 0 end) as 权限c3, sum(case when 权限 = 'C4' then 1 else 0 end) as 权限c4, sum(case when 权限 = 'C5' then 1 else 0 end) as 权限c5 from 表 group by 姓名, 用户ID
create table test(姓名 char(10), 权限 char(10), 权限ID char(10), 用户ID char(10)) insert test select 'A','C1','K1','40' union all select 'A','C2','K2','40' union all select 'B','C3','K3','44' union all select 'C','C4','K4','41' union all select 'C','C5','K5','41' declare @sql varchar(8000) set @sql = 'select 姓名,用户ID' select @sql = @sql + ',(case 权限ID when '''+权限ID+''' then ''√'' else '''' end) ['+权限ID+']' from (select distinct 权限ID from test) as a select @sql = @sql+' from test ' --group by 姓名,用户ID' --print @sql exec(@sql)drop table test 姓名 用户ID K1 K2 K3 K4 K5 ---------- ---------- ---------- ---------- ---------- ---------- ---------- A 40 √ A 40 √ B 44 √ C 41 √ C 41 √
declare @sql varchar(8000) set @sql = 'select 姓名,用户ID' select @sql = @sql + ',max(case 权限ID when '''+权限ID+''' then ''√'' end) ['+权限ID+']' from (select distinct 权限ID from 表) as a select @sql = @sql+' from 表 group by 姓名,用户ID'exec(@sql) go
sum(case when 权限 = 'C1' then 1 else 0 end) as 权限c1,
sum(case when 权限 = 'C2' then 1 else 0 end) as 权限c2,
sum(case when 权限 = 'C3' then 1 else 0 end) as 权限c3,
sum(case when 权限 = 'C4' then 1 else 0 end) as 权限c4,
sum(case when 权限 = 'C5' then 1 else 0 end) as 权限c5
from 表
group by 姓名, 用户ID
insert test select 'A','C1','K1','40'
union all select 'A','C2','K2','40'
union all select 'B','C3','K3','44'
union all select 'C','C4','K4','41'
union all select 'C','C5','K5','41'
declare @sql varchar(8000)
set @sql = 'select 姓名,用户ID'
select @sql = @sql + ',(case 权限ID when '''+权限ID+''' then ''√'' else '''' end) ['+权限ID+']'
from (select distinct 权限ID from test) as a
select @sql = @sql+' from test ' --group by 姓名,用户ID'
--print @sql
exec(@sql)drop table test
姓名 用户ID K1 K2 K3 K4 K5
---------- ---------- ---------- ---------- ---------- ---------- ----------
A 40 √
A 40 √
B 44 √
C 41 √
C 41 √
set @sql = 'select 姓名,用户ID'
select @sql = @sql + ',max(case 权限ID when '''+权限ID+''' then ''√'' end) ['+权限ID+']'
from (select distinct 权限ID from 表) as a
select @sql = @sql+' from 表 group by 姓名,用户ID'exec(@sql)
go