oracle 行列转置 sql 语句写法

[snreo]

查询到的原始数据如上图，想经过行列转置后得到如下图的结果：




这样的语句该怎么写，非常感谢各位高手的帮忙。。。

[LongRui888]

我写个case when的：

with tmp as
(select 'AKS' as aera,'A' as grade from dual 
union all
select 'AKS' as aera,'B' as grade from dual 
union all
select 'AKS' as aera,'C' as grade from dual 
union all
select 'AKS' as aera,'D' as grade from dual 
union all
select 'AKS' as aera,'T' as grade from dual 
union all
select 'YL' as aera,'A' as grade from dual 
union all
select 'YL' as aera,'B' as grade from dual 
union all
select 'YL' as aera,'C' as grade from dual 
union all
select 'YL' as aera,'D' as grade from dual 
union all
select 'YL' as aera,'V' as grade from dual 
union all
select 'WLMQ' as aera,'A' as grade from dual 
union all
select 'WLMQ' as aera,'B' as grade from dual 
union all
select 'WLMQ' as aera,'C' as grade from dual 
union all
select 'WLMQ' as aera,'D' as grade from dual 
union all
select 'WLMQ' as aera,'T' as grade from dual 
union all
select 'WLMQ' as aera,'V' as grade from dual
)

select aera,
count(case when grade='A'then 1 end) AS A,
count(case when grade='B'then 1 end) AS B,
count(case when grade='C'then 1 end) AS C,
count(case when grade='D'then 1 end) AS D,
count(case when grade='T'then 1 end) AS T,
count(case when grade='V'then 1 end) AS V
 from tmp 
group by aera
/*
aera	A	B	C	D	T	V
AKS	1	1	1	1	1	0
WLMQ	1	1	1	1	1	1
YL	1	1	1	1	0	1
*/

[chengccy]

with tmp as
(select 'AKS' as aera,'A' as grade from dual 
union all
select 'AKS' as aera,'B' as grade from dual 
union all
select 'AKS' as aera,'C' as grade from dual 
union all
select 'AKS' as aera,'D' as grade from dual 
union all
select 'AKS' as aera,'T' as grade from dual 
union all
select 'YL' as aera,'A' as grade from dual 
union all
select 'YL' as aera,'B' as grade from dual 
union all
select 'YL' as aera,'C' as grade from dual 
union all
select 'YL' as aera,'D' as grade from dual 
union all
select 'YL' as aera,'V' as grade from dual 
union all
select 'WLMQ' as aera,'A' as grade from dual 
union all
select 'WLMQ' as aera,'B' as grade from dual 
union all
select 'WLMQ' as aera,'C' as grade from dual 
union all
select 'WLMQ' as aera,'D' as grade from dual 
union all
select 'WLMQ' as aera,'T' as grade from dual 
union all
select 'WLMQ' as aera,'V' as grade from dual)
select * from tmp pivot(count(1) for grade in ('A' as A,'B'as B,'C' as C,'D' as D,'T' as T,'V' as V))

11g 环境

with tmp as
(select 'AKS' as aera,'A' as grade from dual 
union all
select 'AKS' as aera,'B' as grade from dual 
union all
select 'AKS' as aera,'C' as grade from dual 
union all
select 'AKS' as aera,'D' as grade from dual 
union all
select 'AKS' as aera,'T' as grade from dual 
union all
select 'YL' as aera,'A' as grade from dual 
union all
select 'YL' as aera,'B' as grade from dual 
union all
select 'YL' as aera,'C' as grade from dual 
union all
select 'YL' as aera,'D' as grade from dual 
union all
select 'YL' as aera,'V' as grade from dual 
union all
select 'WLMQ' as aera,'A' as grade from dual 
union all
select 'WLMQ' as aera,'B' as grade from dual 
union all
select 'WLMQ' as aera,'C' as grade from dual 
union all
select 'WLMQ' as aera,'D' as grade from dual 
union all
select 'WLMQ' as aera,'T' as grade from dual 
union all
select 'WLMQ' as aera,'V' as grade from dual)
select aera,
count(decode(grade,'A',1,NULL)) AS A,
count(decode(grade,'B',1,NULL)) AS B,
count(decode(grade,'C',1,NULL)) AS C,
count(decode(grade,'D',1,NULL)) AS D,
count(decode(grade,'T',1,NULL)) AS T,
count(decode(grade,'V',1,NULL)) AS V
 from tmp 
group by aera

10g 环境,也可以用case when