Oracle数据库行转列与列转行操作实战

"Oracle数据库中的行转列与列转行技术是数据库操作中常见的需求，尤其是在数据分析和报表展示时。本文将详细介绍这两种转换方法，并提供实际的SQL代码示例。 行转列，即把数据库中的一行数据转换为多列显示，通常用于将具有相同属性但不同值的数据集中展示。在Oracle中，可以使用`DECODE`函数配合`SUM`和`GROUP BY`来实现。例如，我们有一个名为`TEST_TB_GRADE`的测试表，包含`ID`（主键）、`USER_NAME`（学生姓名）、`COURSE`（科目）和`SCORE`（分数）字段。如果我们想根据学生姓名和科目显示每个学生的总分，可以编写以下SQL： ```sql select t.user_name, sum(decode(t.course, '语文', score, null)) as CHINESE, sum(decode(t.course, '数学', score, null)) as MATH, sum(decode(t.course, '英语', score, null)) as ENGLISH from test_tb_grade t group by t.user_name order by t.user_name; ``` 这个查询将按学生姓名分组，然后使用`DECODE`函数计算每个学生在语文、数学和英语三科的总分。 进一步扩展，如果需要统计各科目不同分数段的学生数量，例如，00-60分、60-80分和80-100分，我们可以创建一个新的列`SCORE_GP`，并再次使用`DECODE`函数。以下是实现这一功能的SQL代码： ```sql select t2.SCORE_GP, sum(decode(t2.course, '语文', COUNTNUM, null)) as CHINESE, sum(decode(t2.course, '数学', COUNTNUM, null)) as MATH, sum(decode(t2.course, '英语', COUNTNUM, null)) as ENGLISH from ( select t.course, case when t.score < 60 then '00-60' when t.score >= 60 and t.score < 80 then '60-80' when t.score >= 80 then '80-100' end as SCORE_GP, count(t.score) as COUNTNUM from test_tb_grade t group by t.course, case when t.score < 60 then '00-60' when t.score >= 60 and t.score < 80 then '60-80' when t.score >= 80 then '80-100' end ) t2 group by t2.SCORE_GP order by t2.SCORE_GP; ``` 这个查询首先将原始数据按照课程和分数段分组，计算出每个分数段的学生人数，然后在外部查询中再次使用`DECODE`函数将这些统计数据按科目分类。 列转行，即把列数据转换为行显示，一般用于处理具有固定数量列的宽表。在Oracle中，可以使用`UNION ALL`或`PIVOT`操作来实现。`UNION ALL`适用于简单情况，而`PIVOT`则适合更复杂的需求，比如动态列转换。这里仅介绍`UNION ALL`的基本用法： 假设我们有一个表`TEST_TB_INFO`，包含`USER_ID`和`INFO_KEY/VALUE`两列，分别表示用户ID和键值对。要将`INFO_KEY`列的多个值转为行，可以这样写： ```sql select user_id, 'INFO_KEY1' as info_key, info_value from TEST_TB_INFO where info_key = 'INFO_KEY1' union all select user_id, 'INFO_KEY2' as info_key, info_value from TEST_TB_INFO where info_key = 'INFO_KEY2'; ``` 这将返回一个结果集，其中`INFO_KEY1`和`INFO_KEY2`的值被分开到单独的行。 Oracle提供了多种行转列和列转行的方法，可以根据实际需求选择合适的技术。在处理大量数据时，合理地转换行列可以帮助优化数据的展示和分析。"