文章摘要
这篇文章介绍了Oracle中用于行转列的四种主要方法：wm_concat、listagg、xmlagg和pivot函数。wm_concat自12g版本起不再支持，listagg用于将多值字段用分号分隔合并显示，而xmlagg则通过将字符串转换为结构化数据，解决listagg拼接过长的问题。pivot函数则用于将行数据按列展开，便于数据展示和分析。
### 语法
- **wm_concat**
```sql
SELECT 需要分组的字段, wmsys.wm_concat(distinct 需要行转列合并展示的字段) FROM 表名 GROUP BY 需要分组的字段;
```
- **listagg**
```sql
SELECT 需要分组的字段, listagg(需要行转列合并展示的字段, ';') WITHIN GROUP (ORDER BY 需要行转列合并展示的字段) FROM 表名 GROUP BY 需要分组的字段;
```
支持 `over` 分析 window功能：
```sql
SELECT 需要分组的字段, listagg(字段, ';') OVER (PARTITION BY 需要分组的字段) FROM 表名;
```
- **xmlagg**
```sql
SELECT 需要分组的字段, xmlagg(xmlparse(content || ';' WELLFORMED) ORDER BY 需要行转列合并展示的字段).getclobval() FROM 表名 GROUP BY 需要分组的字段;
```
使用 `rtrim` 和 `dbms_lob.substr` 处理拼接后的字符串：
```sql
SELECT 需要分组的字段, rtrim(xmlagg(...).getclobval(), ';') FROM 表名 GROUP BY 需要分组的字段;
```
```sql
SELECT 需要分组的字段, dbms_lob.substr(rtrim(...), 1000, 1) FROM 表名 GROUP BY 需要分组的字段;
```
- **pivot函数**
```sql
SELECT * FROM 表名 PIVOT(聚合函数(字段) FOR 分组字段 IN (值1 AS 标签名1, 值2 AS 标签名2, ..., 值n AS 标签名n));
```
示例：
```sql
SELECT * FROM GRADE_STATISTICS PIVOT(sum(STUDENTS_CNT) FOR GRADE IN ('一年级' AS 一年级, '二年级' AS 二年级, '三年级' AS 三年级, '四年级' AS 四年级));
```
这篇文章总结了Oracle行转列方法的使用场景、语法和注意事项，帮助用户高效地将多值列转换为单值列，适用于数据展示和分析的需求。
目录一、wm_concat函数（oracle12g版本开始不支持）二、listagg函数三、xmlagg函数（解决listagg函数字符串拼接过长问题）四、pivot函数（拆分列）总结
wm_concat、listagg、xmlagg、pivot函数
语法：
select 需要分组的字段,wmsys.wm_concat(distinct 需要行转列合并展示的字段) from 表名 group by 需要分组的字段;
实战：查询各年级涉及的班级，多个班级默认用“,”隔开按一行合并展示。
select * from CLASS_STATISTICS;
select GRADE,wmsys.wm_concat(distinct CLASS) from CLASS_STATISTICS group by GRADE;
语法：
select 需要分组的字段,listagg(需要行转列合并展示的字段,’;’)within group(order by 需要行转列合并展示的字段) from 表名 group by 需要分组的字段;
实战：查询各年级涉及的班级，多个班级用“;”隔开按一行合并展示。
select GRADE,listagg(CLASS,’;’)within group(order by CLASS) from CLASS_STATISTICS group by GRADE;
将group by换成over(partition by)，不会去重
select GRADE,listagg(CLASS,’;’)within group(order by CLASS) over(partition by GRADE) from CLASS_STATISTICS;
语法：
select 需要分组的字段,xmlagg(xmlparse(content 需要行转列合并展示的字段||’;’ wellformed) order by 需要行转列合并展示的字段).getclobval() from 表名 group by 需要分组的字段;
实战：查询各年级涉及的班级，多个班级用“;”隔开按一行合并展示。
select GRADE,xmlagg(xmlparse(content CLASS||’;’ wellformed) order by CLASS).getclobval() from CLASS_STATISTICS group by GRADE;
使用rtrim(字段名,’字符’)，去掉结尾多出来的“;”
select GRADE,rtrim(xmlagg(xmlparse(content CLASS||’;’ wellformed) order by CLASS).getclobval(),’;’) from CLASS_STATISTICS group by GRADE;
使用dbms_lob.substr(字段名,截取字段长度,从第几位开始截取)，将大字段clob字段转换为字符串展示
select GRADE,dbms_lob.substr(rtrim(xmlagg(xmlparse(content CLASS||’;’ wellformed) order by CLASS).getclobval(),’;’),1000,1) from CLASS_STATISTICS group by GRADE;
语法：
select * from 表名 pivot(聚合函数(需要按行展示的字段) for 需要转列展示的字段 in (‘内容1′ AS 字段别名1,’内容2′ AS 字段别名2,’内容n’ AS 字段别名n));
实战：将年级按列展示其学生人数。
select * from GRADE_STATISTICS;
select * from GRADE_STATISTICS pivot(sum(STUDENTS_CNT) for GRADE in (‘一年级’ AS 一年级,’二年级’ AS 二年级,’三年级’ AS 三年级,’四年级’ AS 四年级));
到此这篇关于oracle行转列方法集合汇总的文章就介绍到这了,更多相关oracle行转列方法内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
您可能感兴趣的文章:Oracle逗号分隔列转行实现方法Oracle的数据表中行转列与列转行的操作实例讲解Oracle实现行列转换的方法分析oracle wm_concat 列转行 逗号分隔从Oracle 表格行列转置说起Oracle实现行转换成列的方法
© 版权声明
文章版权归作者所有，未经允许请勿转载。
