oracle行列转置转换

最新推荐文章于 2024-07-31 14:31:55 发布

原创最新推荐文章于 2024-07-31 14:31:55 发布 · 250 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

oracle 专栏收录该内容

11 篇文章

订阅专栏

本文介绍三种不同的方法来实现SQL数据的转换，包括一种较为复杂的高级方法及两种常规实用的方法，适用于需要将表格数据进行重组的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

纯粹是为自己以后使用方便；

本文系转载：http://www.cnblogs.com/fjfzhkb/archive/2007/11/30/978307.html

感谢网友提供！

转换前：
select * from test；
A B C
-- -- --
1 2 3
2 2 4
4 3 3
3 1 6
4 3 6
6 5 9
8 8 8
转换后：
A            B              C               D             E
------------ -------------- --------------- ------------- -------------
1            2              4               3             4
2            2              3               1             3
3            4              3               6             6
方法：（有三种）

--牛逼的方法（悟其精髓确有难度）
select regexp_substr(str, '[^,]+', 1, 1) a,

         regexp_substr(str, '[^,]+', 1, 2) b,
         regexp_substr(str, '[^,]+', 1, 3) c,
         regexp_substr(str, '[^,]+', 1, 4) d,
         regexp_substr(str, '[^,]+', 1, 5) e
    from (select regexp_substr(str, '[^(\.)]+', 1, rownum) str
            from (select max(a) || '.' || max(b) || '.' || max(c) str
                    from (select substr(sys_connect_by_path(a, ','), 2) a,
                                 substr(sys_connect_by_path(b, ','), 2) b,
                                 substr(sys_connect_by_path(c, ','), 2) c
                            from (select rownum child,
                                         a,
                                         b,
                                         c,
                                         lead(rownum, 1) over(order by rownum) parent
                                    from test) t
                           start with child = 1
                          connect by prior parent = child))
          connect by rownum < length(regexp_replace(str, '[^(\.)]', '')) + 2);
--正常的方法（大家都想的到）
select *
    from (select a a1,
                 lead(a, 1) over(order by rownum) a2,
                 lead(a, 2) over(order by rownum) a3,
                 lead(a, 3) over(order by rownum) a4,
                 lead(a, 4) over(order by rownum) a5,
                 lead(a, 5) over(order by rownum) a6
            from test)
   where rownum = 1
union
select *
    from (select b a1,
                 lead(b, 1) over(order by rownum) a2,
                 lead(b, 2) over(order by rownum) a3,
                 lead(b, 3) over(order by rownum) a4,
                 lead(b, 4) over(order by rownum) a5,
                 lead(b, 5) over(order by rownum) a6
            from test)
   where rownum = 1
union
select *
    from (select c a1,
                 lead(c, 1) over(order by rownum) a2,
                 lead(c, 2) over(order by rownum) a3,
                 lead(c, 3) over(order by rownum) a4,
                 lead(c, 4) over(order by rownum) a5,
                 lead(c, 5) over(order by rownum) a6
            from test)
   where rownum = 1;
--方法太多了（你对oracle熟悉么？）
  select max(decode(rn, 1, a, null)) id1,
         max(decode(rn, 2, a, null)) id2,
         max(decode(rn, 3, a, null)) id3,
         max(decode(rn, 4, a, null)) id4,
         max(decode(rn, 5, a, null)) id5,
         max(decode(rn, 6, a, null)) id6
    from (select a.*, rownum rn from test a)
union
select max(decode(rn, 1, b, null)) id1,
         max(decode(rn, 2, b, null)) id2,
         max(decode(rn, 3, b, null)) id3,
         max(decode(rn, 4, b, null)) id4,
         max(decode(rn, 5, b, null)) id5,
         max(decode(rn, 6, b, null)) id6
    from (select a.*, rownum rn from test a)
union
select max(decode(rn, 1, c, null)) id1,
         max(decode(rn, 2, c, null)) id2,
         max(decode(rn, 3, c, null)) id3,
         max(decode(rn, 4, c, null)) id4,
         max(decode(rn, 5, c, null)) id5,
         max(decode(rn, 6, c, null)) id6
    from (select a.*, rownum rn from test a);
end p_test_row;