表转置(转)

最新推荐文章于 2020-07-09 15:56:34 发布

citian0996

最新推荐文章于 2020-07-09 15:56:34 发布

阅读量98

点赞数

表转置

源表如下：
时间 A B C D
2006  1 2 3 4
2005  6 7 8 9
如何转成
项目 2006 2005
A    1    6
B    2    7
C    3    8
D    4    9

SQL> select decode(rn, 1, 'A', 2, 'B', 3, 'C', 4, 'D') item,
  2       sum(case when t='2005' then decode(rn, 1, a, 2, b, 3, c, 4, d) end) "2005",
  3       sum(case when t='2006' then decode(rn, 1, a, 2, b, 3, c, 4, d) end) "2006"
  4    from  test, (select rownum rn from dual connect by rownum <= 4)
  5    group by decode(rn, 1, 'A', 2, 'B', 3, 'C', 4, 'D')
  6    order by 1;

I    2005    2006
- ---------- ----------
A       1       5
B       2       6
C       3       7
D       4       8

表b中有如下数据：
id    pid    code name
1                01       a
2    1       0101    a1
3    1       0102    a2
4    2       010101 a11
5    3       010201 a21
6    1       0103    a3

是一个有树型结构的数据，每一层的pid就是它的父结点，如果pid为空，就是根结点了，现在想要得到的数据如下：
id code1  name1  code2  name2  code3  name3
1    01    a             01       a       01       a
2    01    a             0101    a1       0101    a1
3    01    a             0102    a2       0102    a2
4    01    a             0101    a1       010101  a11
5    01    a             0102    a2       010201  a21
6    01    a             0103    a3       0103    a3

SELECT id
   ,SUBSTR(code_path,INSTR(code_path,'\',1,1)+1,INSTR(code_path,'\',1,2)-INSTR(code_path,'\',1,1)-1) as code1
   ,CASE WHEN l>=2 THEN SUBSTR(code_path,INSTR(code_path,'\',1,2)+1,INSTR(code_path,'\',1,3)-INSTR(code_path,'\',1,2)-1)
         ELSE code
   END as code2
   ,CASE WHEN l>=3 THEN SUBSTR(code_path,INSTR(code_path,'\',1,3)+1,INSTR(code_path,'\',1,4)-INSTR(code_path,'\',1,3)-1)
         ELSE code
   END  as code3
   ,SUBSTR(name_path,INSTR(name_path,'\',1,1)+1,INSTR(name_path,'\',1,2)-INSTR(name_path,'\',1,1)-1) as code1
   ,CASE WHEN l>=2 THEN SUBSTR(name_path,INSTR(name_path,'\',1,2)+1,INSTR(name_path,'\',1,3)-INSTR(name_path,'\',1,2)-1)
         ELSE name
   END as name2
   ,CASE WHEN l>=3 THEN SUBSTR(name_path,INSTR(name_path,'\',1,3)+1,INSTR(name_path,'\',1,4)-INSTR(name_path,'\',1,3)-1)
         ELSE name
   END  as name3
  FROM (
SELECT id
   ,LEVEL l
   ,code
   ,name
   ,SYS_CONNECT_BY_PATH(code,'\')||'\' AS code_path
   ,SYS_CONNECT_BY_PATH(name,'\')||'\' AS name_path
  FROM b
START WITH pid IS NULL
CONNECT BY pid = PRIOR id
);

SELECT id
   ,REGEXP_SUBSTR(code_path,'[^\\]+',1,1) as code1
   ,REGEXP_SUBSTR(code_path,'[^\\]+',1,LEAST(l,2)) as code2
   ,REGEXP_SUBSTR(code_path,'[^\\]+',1,LEAST(l,3)) as code3
   ,REGEXP_SUBSTR(name_path,'[^\\]+',1,1) as name1
   ,REGEXP_SUBSTR(name_path,'[^\\]+',1,LEAST(l,2)) as name2
   ,REGEXP_SUBSTR(name_path,'[^\\]+',1,LEAST(l,3)) as name3
  FROM (
SELECT id
   ,LEVEL l
   ,code
   ,name
   ,SYS_CONNECT_BY_PATH(code,'\')||'\' AS code_path
   ,SYS_CONNECT_BY_PATH(name,'\')||'\' AS name_path
  FROM b
START WITH pid IS NULL
CONNECT BY pid = PRIOR id
);