oracle分解字段转成行

<think>我们参考用户提供的引用资料，特别是引用[1]和引用[4]中关于行转列的方法，以及引用[2]和引用[3]中关于列转行的方法。用户意图：了解SQL中如何实现列转行和行转列的操作。行转列（Pivot）：将多行数据转换为一行中的多列，常用于汇总数据。列转行（Unpivot）：将一行中的多列数据转换为多行，常用于数据分解。我们将分别介绍这两种操作的实现方法，并给出示例。一、行转列（Pivot）方法1：使用CASE WHEN和GROUPBY（通用方法，适用于所有SQL数据库）示例数据：假设有表student1，包含name, subject, score三个字段，数据如下：name|subject |score------|-------| -----张三|语文|65张三|数学|78张三|英语|93李四|语文|76李四|数学|87李四|英语|90我们想要将每个学生的各科成绩转换成一行，即每行显示一个学生的所有科目成绩。SQL语句如下：```sqlSELECT name,MAX(CASE WHENsubject='语文'THEN scoreELSE0 END) AS"语文",MAX(CASE WHENsubject='数学'THEN scoreELSE0 END) AS"数学",MAX(CASE WHENsubject='英语'THEN scoreELSE0 END) AS"英语" FROMstudent1GROUPBY name; ```这里使用MAX函数是因为在分组后，每个学生每个科目只有一条记录（如果有多个则取最大值，如果只有一个则就是这个值）。也可以使用SUM，但需要确保每个科目每个学生只有一条记录。方法2：使用数据库特定的PIVOT函数（如SQLServer、Oracle等支持） SQLServer示例： ```sqlSELECT *FROM(SELECTname,subject,score FROMstudent1) ASsourceTablePIVOT(MAX(score)FOR subjectIN (语文,数学,英语))AS pivotTable;```二、列转行（Unpivot）方法1：使用UNION ALL（通用方法）示例数据：假设有表tb_colrow，包含name,Apple,Banana,Orange,Pear字段，数据如下：name|Apple |Banana |Orange |Pear------| -----| ------| ------| -----张三|10|20|30|40李四|15|25|35|45我们想要将每个水果的列转成行，即每个学生每个水果一行。 SQL语句如下： ```sqlSELECT name, 'Apple'AS fruit, AppleAS amountFROM tb_colrowUNION ALLSELECTname,'Banana',Banana FROMtb_colrowUNIONALLSELECT name, 'Orange',Orange FROMtb_colrowUNIONALLSELECT name, 'Pear',Pear FROMtb_colrowORDER BYname,fruit;```方法2：使用数据库特定的UNPIVOT函数（如SQL Server、Oracle等支持） SQLServer示例： ```sqlSELECT name, fruit, amountFROMtb_colrowUNPIVOT(amountFOR fruitIN (Apple,Banana,Orange,Pear)) ASunpivotTable;```另外，引用[3]中还提到了一种使用XML转换的方法（适用于SQLServer），但相对复杂，这里不详细展开。注意事项：1.行转列时，需要明确知道要转置的列名（如语文、数学、英语），如果列名是动态的，可能需要使用动态SQL。2.列转行时，同样需要明确列名，如果列名很多，使用UNIONALL会比较冗长，但通用性好。以上是SQL中实现行转列和列转行的常用方法。具体使用哪种方法取决于数据库系统以及具体需求。根据问题，我们生成以下相关问题：§§相关问题§§1.行转列操作中，如果科目是动态变化的（即不确定有多少个科目），如何实现？ 2.列转行操作中，如果列非常多，有什么简便的写法？3.在MySQL中如何实现行转列和列转行？ 4.行转列和列转行在性能上有什么需要注意的地方？</think>### SQL中的行转列与列转行操作 行转列（Pivot）和列转行（Unpivot）是SQL中常用的数据转换技术，主要用于报表生成和数据预处理。以下是具体实现方法： #### 一、行转列（Pivot） **场景**：将多行数据按类别转换为单行的多列（如学生各科成绩横向展示） **方法1：CASE WHEN + GROUP BY（通用方案）** ```sql SELECT name, MAX(CASE WHEN subject='语文' THEN score END) AS "语文", MAX(CASE WHEN subject='数学' THEN score END) AS "数学", MAX(CASE WHEN subject='英语' THEN score END) AS "英语" FROM student_x GROUP BY name; ``` *结果示例：* | name | 语文 | 数学 | 英语 | |------|------|------|------| | 张三 | 65 | 78 | 93 | | 李四 | 76 | 87 | 90 | **方法2：PIVOT函数（SQL Server/Oracle专用）** ```sql SELECT * FROM student_x PIVOT (MAX(score) FOR subject IN ('语文','数学','英语')); ``` #### 二、列转行（Unpivot） **场景**：将多列数据转换为多行（如水果销售数据纵向展示） **方法1：UNION ALL（通用方案）** ```sql SELECT name, 'Apple' AS fruit, Apple AS amount FROM tb_colrow UNION ALL SELECT name, 'Banana', Banana FROM tb_colrow UNION ALL SELECT name, 'Orange', Orange FROM tb_colrow; ``` *结果示例：* | name | fruit | amount | |------|--------|--------| | 张三 | Apple | 10 | | 张三 | Banana | 20 | | 李四 | Apple | 15 | **方法2：UNPIVOT函数（SQL Server/Oracle专用）** ```sql SELECT name, fruit, amount FROM tb_colrow UNPIVOT (amount FOR fruit IN (Apple, Banana, Orange)); ``` #### 三、特殊场景处理 1. **动态列名转换**（如不固定科目数量） 需使用存储过程动态生成SQL，通过`EXECUTE`执行（引用[3]的XML方法适用于SQL Server）： ```sql DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX); SELECT @cols = STRING_AGG(QUOTENAME(subject), ',') FROM (SELECT DISTINCT subject FROM student_x) t; SET @sql = N'SELECT name, ' + @cols + ' FROM student_x PIVOT (MAX(score) FOR subject IN (' + @cols + ')) p'; EXEC sp_executesql @sql; ``` 2. **多值字段拆解**（如技能列表拆分） 使用字符串函数（引用[3]）： ```sql SELECT name, skill_split FROM ( SELECT name, CAST('<root><v>' + REPLACE(skill, '-', '</v><v>') + '</v></root>' AS XML) AS xml_data FROM character_skills ) t CROSS APPLY ( SELECT skill_split = N.v.value('.', 'varchar(100)') FROM t.xml_data.nodes('/root/v') N(v) ) s; ``` > **性能建议**： > - 行转列优先使用`MAX()`聚合避免空值干扰（引用[4]） > - 大数据量时列转行用`UNION ALL`替代`UNION` > - 定期更新统计信息以优化执行计划