create table test(c1 char(1),c2 char(1),c3 char(1),c4 char(1),c5 char(1))
go
declare @n int = 1;
while(@n < 1000)
begin
insert into test values(char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)),char(65+((cast(rand()*100 as int)) % 26)));
set @n += 1;
end
create table [user](name char(1));
go
insert into [user]
select 'A' as name
union all select 'B'
union all select 'C'
union all select 'D'
union all select 'E'
union all select 'F'
union all select 'G'
union all select 'H'
union all select 'I'
union all select 'J'
union all select 'K'
union all select 'L'
union all select 'M'
union all select 'N'
union all select 'O'
union all select 'P'
union all select 'Q'
union all select 'R'
union all select 'S'
union all select 'T'
union all select 'U'
union all select 'V'
union all select 'W'
union all select 'X'
union all select 'Y'
union all select 'Z'
GO
select t.name,sum(t.c1Cnt) as c1,sum(t.c2Cnt) as c2,sum(t.c3Cnt) as c3,sum(t.c4Cnt) as c4,sum(t.c5Cnt) as c5 from
(
select u.name, case when t.c1 = u.name then t.Cnt end as c1Cnt
,case when t.c2 = u.name then t.Cnt end as c2Cnt
,case when t.c3 = u.name then t.Cnt end as c3Cnt
,case when t.c4 = u.name then t.Cnt end as c4Cnt
,case when t.c5 = u.name then t.Cnt end as c5Cnt
from [user] u,(select c1,c2,c3,c4,c5,count(1) as Cnt
from test
group by grouping sets((c1),(c2),(c3),(c4),(c5))) as t
) as t
group by t.name
以上示例只是应一位大虾写出来的,在大数据量操作上,不敢恭维.
本示例纯属戏作.正规应用下,要保证数据量足够小,方可使用.