交叉表实现一例
问题 ：
源表数据：
ID NAME CODE
1 A AA
2 A BB
3 B CC
4 B DD
5 C EE
6 C FF
7 D GG
8 D HH
9 E II
10 E JJ
希望统计成如下格式：
CODE A C D E F 合计
---------- ----------- ----------- ----------- ----------- ----------- -----
AA 1 0 0 0 0 10%
BB 1 0 0 0 0 10%
CC 0 1 0 0 0 10%
DD 0 1 0 0 0 10%
EE 0 0 1 0 0 10%
FF 0 0 1 0 0 10%
GG 0 0 0 1 0 10%
HH 0 0 0 1 0 10%
II 0 0 0 0 1 10%
JJ 0 0 0 0 1 10%
实现步骤如下：
--建立测试数据
declare @tmp1 table
(
id int,
name varchar(10),
code varchar(10)
)
insert into @tmp1
select 1,'A','AA'
UNION
select 2,'A','BB'
UNION
select 3,'B','CC'
UNION
select 4,'B','DD'
UNION
select 5,'C','EE'
UNION
select 6,'C','FF'
UNION
select 7,'D','GG'
UNION
select 8,'D','HH'
UNION
select 9,'E','II'
UNION
select 10,'E','JJ'
--返回结果
SELECT CODE,
sum(A) as 'A',
sum(B) as 'C',
sum(C) as 'D',
sum(D) as 'E',
sum(E) as 'F',
left(CAST(COUNT(CODE) AS DECIMAL(10,2))/(SELECT COUNT(*) FROM @TMP1)*100,2) +'%' AS '合计'
FROM
(SELECT CODE ,
CASE WHEN NAME='A' THEN count(CODE) ELSE 0 end as A,
CASE WHEN NAME='B' THEN count(CODE) ELSE 0 end AS B,
CASE WHEN NAME='C' THEN count(CODE) ELSE 0 end AS C,
CASE WHEN NAME='D' THEN count(CODE) ELSE 0 end AS D,
CASE WHEN NAME='E' THEN count(CODE) ELSE 0 end AS E
FROM @tmp1
GROUP BY CODE,name) AS C
GROUP BY CODE
