求sql结果集转换写法

[pingkeke]

有一数据集如下：
等级1 等级2 比例(%)
C2L C1F 10%
C2L C2F 90%
C3F C3F 100%
X1F X2F 20%
X1L X2F 80%
X2F X2F 90%
X2F CC4F 10%
C3F CC4F 15%
C3L CC4F 32%
C3F CC3F 85%
C3L CC3F 68%
X1F CC3F 80%
X1L CC3F 20%
怎么SQL写成这种交叉格式的数据集

C2F C3F X2F CC4F CC3F C1F 总计
C3F 0.00% 50.00% 0.00% 7.50% 42.50% 0.00% 100.00%
X2F 0.00% 0.00% 90.00% 10.00% 0.00% 0.00% 100.00%
X1F 0.00% 0.00% 20.00% 0.00% 80.00% 0.00% 100.00%
C3L 0.00% 0.00% 0.00% 32.00% 68.00% 0.00% 100.00%
C2L 90.00% 0.00% 0.00% 0.00% 0.00% 10.00% 100.00%
X1L 0.00% 0.00% 80.00% 0.00% 20.00% 0.00% 100.00%
总计 12.86% 14.29% 27.14% 8.14% 36.14% 1.43% 100.00%

注意两头总计相加要100.00%

[Andy__Huang]

create table #tb(level1 varchar(10),level2 varchar(10),rate numeric(12,2))
insert into #tb
select 'C2L','C1F',10
union all select 'C2L','C2F',90
union all select 'C3F','C3F',100
union all select 'X1F','X2F',20
union all select 'X1L','X2F',80
union all select 'X2F','X2F',90
union all select 'X2F','CC4F',10
union all select 'C3F','CC4F',15
union all select 'C3L','CC4F',32
union all select 'C3F','CC3F',85
union all select 'C3L','CC3F',68
union all select 'X1F','CC3F',80
union all select 'X1L','CC3F',20

declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(level2)+']=cast(isnull(sum(case level2 when '''+rtrim(level2)+''' then rate end),0) as varchar)+''%'''
from #tb group by level2
exec('select level1'+@sql+',sum(rate)  as 总计 from  #tb group by level1 UNION ALL select ''总计'' as 总计'+@sql+',sum(rate) from  #tb ' )

drop table #tb

你给的数据好象有问题?

[华夏小卒]

等级2不固定，或者比较多的话，就要搞1楼那种动态的