求一条分类SQL

Question

有表 TABLE记录如下：name    value   pn
=============================
A       10000   100
A        8000    90
A        7000    80
..........................
B        100    100
B         90     90
B         80     80
..........................
C        95      100
...........................  
C         15      0     其中pn均为的值均为从0到100，每隔10，共11条记录现经过SQL后的列表如下：name  100     90     80   70   60   50   40   30   20   10   0
==========================================================
A    10000  8000  7000 .....................................
B      100    90    80 ........................................
C       90   ........................................        15 
..............................................
SQL该如何写???

Answer 1

create table test( name varchar2(10),value int,pn int);
/
insert into test
select 'A',10000,100 from dual
union all
select 'A',8000,90 from dual
union all
select 'A',7000,80 from dual
union all
select 'B',100,100 from dual
union all
select 'B',90,90 from dual
union all
select 'B',80,80 from dual
union all
select 'C',95,100 from dual
union all
select 'C',15,0 from dual; 
/
select 
name,
sum(decode(pn,100,value,0)) "100",
sum(decode(pn,90,value,0)) "90",
sum(decode(pn,80,value,0)) "80",
sum(decode(pn,70,value,0)) "70",
sum(decode(pn,60,value,0)) "60",
sum(decode(pn,50,value,0)) "50",
sum(decode(pn,40,value,0)) "40",
sum(decode(pn,30,value,0)) "30",
sum(decode(pn,20,value,0)) "20",
sum(decode(pn,10,value,0)) "10",
sum(decode(pn,0,value,0)) "0"
from test
group by name,pn 
order by name,pn desc
/
--Result
1 A 10000 0 0 0 0 0 0 0 0 0 0
2 A 0 8000 0 0 0 0 0 0 0 0 0
3 A 0 0 7000 0 0 0 0 0 0 0 0
4 B 100 0 0 0 0 0 0 0 0 0 0
5 B 0 90 0 0 0 0 0 0 0 0 0
6 B 0 0 80 0 0 0 0 0 0 0 0
7 C 95 0 0 0 0 0 0 0 0 0 0
8 C 0 0 0 0 0 0 0 0 0 0 15

Answer 2

多谢！试用hongqi162(失踪的月亮) ，明显有问题。一是SELECT出来的值全部为0
二是我要求的结果是NAME唯一。你给的结果不唯一。

Answer 3

不用0显示你想用什么显示
select 
name,
sum(decode(pn,100,value,0)) "100",
sum(decode(pn,90,value,0)) "90",
sum(decode(pn,80,value,0)) "80",
sum(decode(pn,70,value,0)) "70",
sum(decode(pn,60,value,0)) "60",
sum(decode(pn,50,value,0)) "50",
sum(decode(pn,40,value,0)) "40",
sum(decode(pn,30,value,0)) "30",
sum(decode(pn,20,value,0)) "20",
sum(decode(pn,10,value,0)) "10",
sum(decode(pn,0,value,0)) "0"
from test
group by name
order by name asc--Result
1 A 10000 8000 7000 0 0 0 0 0 0 0 0
2 B 100 90 80 0 0 0 0 0 0 0 0
3 C 95 0 0 0 0 0 0 0 0 0 15

Answer 4

把楼上的这些
sum(decode(pn,90,value,0)) "90"
换成
case when pn = '90' then sum(value) end "90"可以解决：SELECT出来的值全部为0
 

Answer 5

hongqi162(失踪的月亮) ( ) 信誉：100    Blog   加为好友 
后面的那个写的是对的!
好多0是因为测试的数据少,没有数据的时候就是0

Answer 6

http://atgc.itpub.net/post/22412/251110