oracle 中行转列问题.高手进来看看.谢谢

Question

搜索了网络上行转换为列的,原来记录都是只有一条
我现在的表机构如下:
  表A 中   字段名    内容    行号
           pname    a      1
           pid      2001   1
           psex     男      1
           pscore   80     1
           pname    b      2
           pid      2002   2
           psex     男      2
           pscore   90     2
           pname    c     3
           pid      2003   3
           psex     女      3
           pscore   85     3现在我想把它转换为列:
 表B     pname    pid    psex   pscore
           a      2001    男       80
           b      2002    男       90
           c      2003    女       85
这个用case  decode 行不通,要如何处理呢? 请高手帮忙,谢谢

Answer 1

create table #(id int,pname char(1),pid varchar(4),psex char(2),pscore float)
go
insert into # values(1,'a', '2001','男',80)
insert into # values(2,'b', '2002','男' ,90)
insert into # values(3,'c','2003','女',85)
go      select id,
max
   (case pname when 'a' then 'a' 
               when 'b' then 'b' 
               when 'c' then 'c' 
               else null 
    end
   )
   pname,
max(case psex when '男' then '男' else '女' end)psex,
cast(avg(pscore*1.0) as decimal(18,2)) pscore
--pscore=(case when pscore<>0 then psocre else 0 end)
from #
group by iddrop table #

Answer 2

SQL> select * from t;COL1     COL2           COL3
-------- -------- ----------
pname    a                 1
pid      2001              1
psex     男                1
pscore   80                1
pname    b                 2
pid      2002              2
psex     男                2
pscore   90                2
pname    c                 3
pid      2003              3
psex     女                3
pscore   85                312 rows selected;
SQL> Select  Max(b1.pname) pname,Max(b1.pid) pid,Max(b1.psex) psex,Max(b1.pscore) pscore
  2  From (Select
  3         decode(a.col1,'pname',a.col2,Null) Pname,
  4         decode(a.col1,'pid',a.col2,Null) Pid,
  5         decode(a.col1,'psex',a.col2,Null)Psex,
  6         decode(a.col1,'pscore',a.col2,Null) Pscore,
  7         col3
  8  From t a
  9  Group By a.col1,a.col2,col3
 10  ) b1
 11  Group By b1.col3
 12  /PNAME    PID      PSEX     PSCORE
-------- -------- -------- --------
a        2001     男       80
b        2002     男       90
c        2003     女       85

Answer 3

select wmsys.wm_contact(内容) from table group by 行号

Answer 4

SQL> select * from t;
 
COL1       COL2                                          COL3
---------- ---------- ---------------------------------------
pname      a                                                1
pid        2001                                             1
psex       男                                                      1
pscore     80                                               1
pname      b                                                2
pid        2002                                             2
psex       男                                                      2
pscore     90                                               2
pname      c                                                3
pid        2003                                             3
psex       女                                                      3
pscore     85                                               3
 
12 rows selected
 
SQL> 
SQL> select col3,
  2  max(case when col1='pname' then col2 end) pname,
  3  max(case when col1='pid' then col2 end) pid,
  4  max(case when col1='psex' then col2 end) psex,
  5  max(case when col1='pscore' then col2 end) pscore
  6  from t
  7  group by col3;
 
                                   COL3 PNAME      PID        PSEX       PSCORE
--------------------------------------- ---------- ---------- ---------- ----------
                                      1 a          2001       男         80
                                      2 b          2002       男         90
                                      3 c          2003       女         85

Answer 5

用max+decode就可以做了
楼上的的case when也一样