Oracle行列转换

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

乘风晓栈

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

阅读量977

点赞数 1

分类专栏： Oracle相关文章标签：行转列

本文链接：https://blog.csdn.net/cheng_feng_xiao_zhan/article/details/82895062

版权

Oracle相关专栏收录该内容

34 篇文章 3 订阅

订阅专栏

11g之后，oracle增加了pivot和unpivot语句，可以很方便的完成这个转换。
没有安装，则没有测试在本文中写出，网上看到一篇很好的文章，地址：https://blog.csdn.net/fw0124/article/details/7943965

后续测试验证后更新。。。

--CTEATE TABLE

CREATE TABLE BB_LINE_COLUMN_T 
(
       TIME_ID          VARCHAR2(32),
       PAY_NAME         VARCHAR2(300),
       PAY_NUM          NUMBER(10),
       PAY_ORDER_NUM    VARCHAR2(64)
);
SELECT * FROM BB_LINE_COLUMN_T;
DELETE FROM BB_LINE_COLUMN_T;
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','water_fee','88','20180929094112110'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','electricity_fee','96','20180929094112111'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','gas_fee','65','20180929094112110'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','room_fee','3000','20180929094112112'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','restaurant_fee','900','20180929094112113'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201809','life_fee','800','20180929094112114'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','water_fee','70','20180829094112115'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','electricity_fee','80','20180829094112116'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','gas_fee','60','20180829094112117'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','room_fee','3000','20180829094112118'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','restaurant_fee','800','20180829094112119'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201808','life_fee','800','20180829094112190'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','water_fee','80','20180729094112191'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','electricity_fee','90','20180729094112192'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','gas_fee','60','20180729094112193'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','room_fee','3000','20180729094112199'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','restaurant_fee','700','20180729094112166'); 
INSERT INTO BB_LINE_COLUMN_T VALUES('201807','life_fee','700','20180729094112177');

表中数据：

        TIME_ID    PAY_NAME    PAY_NUM    PAY_ORDER_NUM
1	201809	water_fee	88	20180929094112110
2	201809	electricity_fee	96	20180929094112111
3	201809	gas_fee	65	20180929094112110
4	201809	room_fee	3000	20180929094112112
5	201809	restaurant_fee	900	20180929094112113
6	201809	life_fee	800	20180929094112114
7	201808	water_fee	70	20180829094112115
8	201808	electricity_fee	80	20180829094112116
9	201808	gas_fee    	60	20180829094112117
10	201808	room_fee	3000	20180829094112118
11	201808	restaurant_fee	800	20180829094112119
12	201808	life_fee	800	20180829094112190
13	201807	water_fee	80	20180729094112191
14	201807	electricity_fee	90	20180729094112192
15	201807	gas_fee    	60	20180729094112193
16	201807	room_fee	3000	20180729094112199
17	201807	restaurant_fee	700	20180729094112166
18	201807	life_fee	700	20180729094112177

需要查询出效果：

        TIME_ID 水费    电费    气费    房费    餐饮费    生活费
1	201807	80	90	60	3000	700	700
2	201808	70	80	60	3000	800	800
3	201809	88	96	65	3000	900	800

1.--行转列
--使用子查询（注意：需要判断所有数据都是一条，例：水费9月交了两次则使用子查询时需要sum/group by两个函数，不要出现笛卡尔乘积现象）

SELECT A.TIME_ID,
       A.PAY_NUM AS 水费,
       B.PAY_NUM AS 电费,
       C.PAY_NUM AS 气费,
       D.PAY_NUM AS 房费,
       E.PAY_NUM AS 餐饮费,
       F.PAY_NUM AS 生活费
  FROM (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'water_fee') A,
       (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'electricity_fee') B,
       (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'gas_fee') C,
       (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'room_fee') D,
       (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'restaurant_fee') E,
       (SELECT TIME_ID, PAY_NUM FROM BB_LINE_COLUMN_T WHERE PAY_NAME = 'life_fee') F
 WHERE A.TIME_ID = B.TIME_ID
   AND B.TIME_ID = C.TIME_ID
   AND C.TIME_ID = D.TIME_ID
   AND D.TIME_ID = E.TIME_ID
   AND E.TIME_ID = F.TIME_ID;

--使用decode函数

SELECT TIME_ID,
       SUM(DECODE(PAY_NAME, 'water_fee', PAY_NUM)) AS 水费,
       SUM(DECODE(PAY_NAME, 'electricity_fee', PAY_NUM)) AS 电费,
       SUM(DECODE(PAY_NAME, 'gas_fee', PAY_NUM)) AS 气费,
       SUM(DECODE(PAY_NAME, 'room_fee', PAY_NUM)) AS 房费,
       SUM(DECODE(PAY_NAME, 'restaurant_fee', PAY_NUM)) AS 餐饮费,
       SUM(DECODE(PAY_NAME, 'life_fee', PAY_NUM)) AS 生活费
  FROM BB_LINE_COLUMN_T T
 GROUP BY TIME_ID
 ORDER BY TIME_ID;

2.--列转行

不新建表，把上边的结果表叫T表进行后续操作

--使用union

WITH T AS (
  SELECT TIME_ID,
         SUM(DECODE(PAY_NAME, 'water_fee', PAY_NUM)) AS 水费,
         SUM(DECODE(PAY_NAME, 'electricity_fee', PAY_NUM)) AS 电费,
         SUM(DECODE(PAY_NAME, 'gas_fee', PAY_NUM)) AS 气费,
         SUM(DECODE(PAY_NAME, 'room_fee', PAY_NUM)) AS 房费,
         SUM(DECODE(PAY_NAME, 'restaurant_fee', PAY_NUM)) AS 餐饮费,
         SUM(DECODE(PAY_NAME, 'life_fee', PAY_NUM)) AS 生活费
    FROM BB_LINE_COLUMN_T T
   GROUP BY TIME_ID
   ORDER BY TIME_ID
)
SELECT TIME_ID,'water_fee' AS PAY_NAME,水费 AS PAY_NUM FROM T UNION
SELECT TIME_ID,'electricity_fee' AS PAY_NAME,电费 AS PAY_NUM FROM T UNION
SELECT TIME_ID,'gas_fee' AS PAY_NAME,气费 AS PAY_NUM FROM T UNION
SELECT TIME_ID,'room_fee' AS PAY_NAME,房费 AS PAY_NUM FROM T UNION
SELECT TIME_ID,'restaurant_fee' AS PAY_NAME,餐饮费 AS PAY_NUM FROM T UNION
SELECT TIME_ID,'life_fee' AS PAY_NAME,生活费 AS PAY_NUM FROM T;

--用到笛卡尔乘积，一行变五行，然后利用decode

WITH T AS (
  SELECT TIME_ID,
         SUM(DECODE(PAY_NAME, 'water_fee', PAY_NUM)) AS 水费,
         SUM(DECODE(PAY_NAME, 'electricity_fee', PAY_NUM)) AS 电费,
         SUM(DECODE(PAY_NAME, 'gas_fee', PAY_NUM)) AS 气费,
         SUM(DECODE(PAY_NAME, 'room_fee', PAY_NUM)) AS 房费,
         SUM(DECODE(PAY_NAME, 'restaurant_fee', PAY_NUM)) AS 餐饮费,
         SUM(DECODE(PAY_NAME, 'life_fee', PAY_NUM)) AS 生活费
    FROM BB_LINE_COLUMN_T T
   GROUP BY TIME_ID
   ORDER BY TIME_ID
)
SELECT TIME_ID,
       DECODE(LVL, 1, 'water_fee', 2, 'electricity_fee', 3, 'gas_fee', 4, 'room_fee', 5, 'restaurant_fee', 6, 'life_fee') AS PAY_NAME,
       DECODE(LVL, 1, 水费, 2, 电费, 3, 气费, 4, 房费, 5, 餐饮费, 6, 生活费) AS PAY_NUM            
  FROM T, (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL <= 6)
 ORDER BY TIME_ID;

希望对你有帮助，祝你有一个好心情，加油！

若有错误、不全、可优化的点，欢迎纠正与补充！