oracle 行转列和列转行的几种方式

骑猪看日落 2024-03-25 15:23 62阅读 0赞

#### 1、准备数据：REST表 ####

-- 创建表REST
    CREATE TABLE REST (
      "ID" NUMBER,
      "AMOUNT" NUMBER(19,0),
      "MONTH" VARCHAR2(255 BYTE)
    );
    
    --执行添加数据语句
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Jan');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Feb');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '66', 'Mar');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '77', 'Jun');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '88', 'Dec');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '12', 'Aug');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '22', 'Feb');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '33', 'Apr');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '232', 'Jul');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '43', 'Sep');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '544', 'Oct');
    INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '65', 'Nov');

#### 2、查询数据 ####

![7b7d7be3749e4a6bbb89e9f9aaf8e52f.png][]

#### 3、行转列 ####

#### 方式1：使用 case when then方式 ####

> case 条件
> 
> when 值1 then 返回值1
> 
> when 值2 then 返回值2
> 
> ..........
> 
> else 默认值
> 
> end

-- 使用case when 方式
    SELECT
    	id,
    	sum( CASE month WHEN 'Jan' THEN amount ELSE 0 END ) AS Jan_amount,
    	sum( CASE month WHEN 'Feb' THEN amount ELSE 0 END ) AS Feb_amount,
    	sum( CASE month WHEN 'Mar' THEN amount ELSE 0 END ) AS Mar_amount,
    	sum( CASE month WHEN 'Apr' THEN amount ELSE 0 END ) AS Apr_amount,
    	sum( CASE month WHEN 'May' THEN amount ELSE 0 END ) AS May_amount,
    	sum( CASE month WHEN 'Jun' THEN amount ELSE 0 END ) AS Jun_amount,
    	sum( CASE month WHEN 'Jul' THEN amount ELSE 0 END ) AS Jul_amount,
    	sum( CASE month WHEN 'Aug' THEN amount ELSE 0 END ) AS Aug_amount,
    	sum( CASE month WHEN 'Sep' THEN amount ELSE 0 END ) AS Sep_amount,
    	sum( CASE month WHEN 'Oct' THEN amount ELSE 0 END ) AS Oct_amount,
    	sum( CASE month WHEN 'Nov' THEN amount ELSE 0 END ) AS Nov_amount,
    	sum( CASE month WHEN 'Dec' THEN amount ELSE 0 END ) AS Dec_amount 
    FROM
    	REST 
    GROUP BY
    	id

> case when 另一种方式：
> 
> case when 条件 = 值1 then 返回值1
> 
> case when 条件 = 值1 then 返回值1
> 
> else 默认值
> 
> end

SELECT
    	id,
    	sum( CASE  WHEN month ='Jan' THEN amount ELSE 0 END ) AS Jan_amount,
    	sum( CASE  WHEN month = 'Feb' THEN amount ELSE 0 END ) AS Feb_amount,
    	sum( CASE  WHEN month = 'Mar' THEN amount ELSE 0 END ) AS Mar_amount,
    	sum( CASE  WHEN month = 'Apr' THEN amount ELSE 0 END ) AS Apr_amount,
    	sum( CASE  WHEN month = 'May' THEN amount ELSE 0 END ) AS May_amount,
    	sum( CASE  WHEN month = 'Jun' THEN amount ELSE 0 END ) AS Jun_amount,
    	sum( CASE  WHEN month = 'Jul' THEN amount ELSE 0 END ) AS Jul_amount,
    	sum( CASE  WHEN month = 'Aug' THEN amount ELSE 0 END ) AS Aug_amount,
    	sum( CASE  WHEN month = 'Sep' THEN amount ELSE 0 END ) AS Sep_amount,
    	sum( CASE  WHEN month = 'Oct' THEN amount ELSE 0 END ) AS Oct_amount,
    	sum( CASE  WHEN month = 'Nov' THEN amount ELSE 0 END ) AS Nov_amount,
    	sum( CASE  WHEN month = 'Dec' THEN amount ELSE 0 END ) AS Dec_amount 
    FROM
    	REST 
    GROUP BY
    	id

结果为：

![8b6f815fbbd9467dad1f9fdf3d35ab70.png][]

#### 方式2： 使用 decode函数 ####

> decode函数： DECODE(条件, 值1, 返回值1, 值2,返回值2, 值3,返回值3, . . . else 缺省值)
> 
> 含义：if 条件 = 值1 then 返回值1 elsif 条件 = 值2 then 返回值2 else (缺省值) endif

--	使用decode函数
    SELECT
    	id,
    	sum( decode( month, 'Jan', amount, 0 ) ) Jan_amount,
    	sum( decode( month, 'Feb', amount, 0 ) ) Feb_amount,
    	sum( decode( month, 'Mar', amount, 0 ) ) Mar_amount,
    	sum( decode( month, 'Apr', amount, 0 ) ) Apr_amount,
    	sum( decode( month, 'May', amount, 0 ) ) May_amount,
    	sum( decode( month, 'Jun', amount, 0 ) ) Jun_amount,
    	sum( decode( month, 'Jul', amount, 0 ) ) Jul_amount,
    	sum( decode( month, 'Aug', amount, 0 ) ) Aug_amount,
    	sum( decode( month, 'Sep', amount, 0 ) ) Sep_amount,
    	sum( decode( month, 'Oct', amount, 0 ) ) Oct_amount,
    	sum( decode( month, 'Nov', amount, 0 ) ) Nov_amount,
    	sum( decode( month, 'Dec', amount, 0 ) ) Dec_amount 
    FROM
    	REST 
    GROUP BY
    	id

结果和方式1一样

![a9b3ae2da73c4d0eb39f75f55bf26097.png][]

#### 方式3：使用pivot函数 ####

> pivot  
> (  
> <聚合函数>(要聚合的列)  
> for <要转换的列> in (要转换的列值 as 要转换成的列名)  
> )

SELECT
    	* 
    FROM
    	 REST pivot (
    		SUM(amount) FOR month IN (
    			'Jan' AS Jan_amount,
    			'Feb' AS Feb_amount,
    			'Mar' AS Mar_amount,
    			'Apr' AS Apr_amount,
    			'May' AS May_amount,
    			'Jun' AS Jun_amount,
    			'Jul' AS Jul_amount,
    			'Aug' AS Aug_amount,
    			'Sep' AS Sep_amount,
    			'Oct' AS Oct_amount,
    			'Nov' AS Nov_amount,
    			'Dec' AS Dec_amount 
    		) 
    	);

结果为：这个结果会发现，如果数据为空没有赋值为0

![c1e3db57c07c428eb0be281d2bcd6728.png][]

下面这个方法解决null 转为0 问题

SELECT
    	NVl(Jan_amount,0) Jan_amount,
    	NVl(Feb_amount,0) Feb_amount,
    	NVl(Mar_amount,0) Mar_amount,
    	NVl(Apr_amount,0) Apr_amount,
    	NVl(May_amount,0) May_amount,
    	NVl(Jun_amount,0) Jun_amount,
    	NVl(Jul_amount,0) Jul_amount,
    	NVl(Aug_amount,0) Aug_amount,
    	NVl(Sep_amount,0) Sep_amount,
    	NVl(Oct_amount,0) Oct_amount,
    	NVl(Nov_amount,0) Nov_amount,
    	NVl(Dec_amount,0) Dec_amount
    FROM
    	 REST pivot (
    		SUM(amount) FOR month IN (
    			'Jan' AS Jan_amount,
    			'Feb' AS Feb_amount,
    			'Mar' AS Mar_amount,
    			'Apr' AS Apr_amount,
    			'May' AS May_amount,
    			'Jun' AS Jun_amount,
    			'Jul' AS Jul_amount,
    			'Aug' AS Aug_amount,
    			'Sep' AS Sep_amount,
    			'Oct' AS Oct_amount,
    			'Nov' AS Nov_amount,
    			'Dec' AS Dec_amount 
    		) 
    	);

结果和方式1一样：

![d4bbd02273874843b6537fee6b63b403.png][]

#### 4、列转行 ####

> 在上述pivot 方法的原sql语句上再加上unpivot函数，将列再转为行，在unpivot函数中，amount：表示由列转换为行后的数据
> 
> month：表示由列转换为行后的列名

select * from REST
    pivot (sum(amount) for month in (
    			'Jan' AS Jan_amount,
    			'Feb' AS Feb_amount,
    			'Mar' AS Mar_amount,
    			'Apr' AS Apr_amount,
    			'May' AS May_amount,
    			'Jun' AS Jun_amount,
    			'Jul' AS Jul_amount,
    			'Aug' AS Aug_amount,
    			'Sep' AS Sep_amount,
    			'Oct' AS Oct_amount,
    			'Nov' AS Nov_amount,
    			'Dec' AS Dec_amount 
    ))
    unpivot ( amount for month in(Jan_amount,Feb_amount,Mar_amount,Apr_amount,May_amount,Jun_amount,Jul_amount,Aug_amount,Sep_amount,Oct_amount,Nov_amount,Dec_amount));

结果为：

![127d479afbdb45999847fdc53a940f19.png][]

#### 5、直接使用unpivot函数 --列转行 ####

准备数据：TEST表

CREATE TABLE TEST (
      "ID" NUMBER(12,0) NOT NULL,
      "JAN" VARCHAR2(255 BYTE),
      "FEB" VARCHAR2(255 BYTE),
      "MAR" VARCHAR2(255 BYTE),
      "APR" VARCHAR2(255 BYTE),
      "MAY" VARCHAR2(255 BYTE),
      "JUN" VARCHAR2(255 BYTE),
      "JUL" VARCHAR2(255 BYTE),
      "AUG" VARCHAR2(255 BYTE),
      "SEP" VARCHAR2(255 BYTE),
      "OCT" VARCHAR2(255 BYTE),
      "NOV" VARCHAR2(255 BYTE),
      "DEC" VARCHAR2(255 BYTE)
    );
    -- 插入数据
    INSERT INTO "CERPAWCSADM"."TEST" VALUES ('1', '33', '2', '3', '4', '5', '6', '7', '8', '9', '99', '8', '6');
    INSERT INTO "CERPAWCSADM"."TEST" VALUES ('2', '22', '3', '4', '6', '5', '7', '0', '7', '22', '21', '343', '76');
    INSERT INTO "CERPAWCSADM"."TEST" VALUES ('3', '88', '3', '4', '5', '7', '9', '7', '2', '2', '231', '56', '78');

查询出的数据

![51910e4b727f45afb1464d51f7761cb3.png][]

列转行sql

SELECT
    	* 
    FROM TEST
    	unpivot ( amount for month in(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC));

结果为：

![4d0367d2bb50471dae052086934aa449.png][]

[7b7d7be3749e4a6bbb89e9f9aaf8e52f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/0374f2029a25437f96c6fcac1d46ebcf.png
[8b6f815fbbd9467dad1f9fdf3d35ab70.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3d4094a874734755a060e0664093ce3b.png
[a9b3ae2da73c4d0eb39f75f55bf26097.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/47f46e9c097b44959a051b7b99720483.png
[c1e3db57c07c428eb0be281d2bcd6728.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/761ca08cac9a4fe8a80b132b92287087.png
[d4bbd02273874843b6537fee6b63b403.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/233fad646db941079354ff28c29dee95.png
[127d479afbdb45999847fdc53a940f19.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/461d73ac75d941c4be2a72404c5c74f0.png
[51910e4b727f45afb1464d51f7761cb3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4e16c93f46cb426898a4745ee31ad76c.png
[4d0367d2bb50471dae052086934aa449.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/638055ae8efc40ffbcc57ede2916a675.png