oracle列转行经典例题

### Oracle 数据库中列转行的实现方法 在 Oracle 数据库中，列转行可以通过 `UNPIVOT` 函数来实现。该函数允许将多列数据转换为单列中的多行数据。以下是具体的语法和示例： #### 语法 ```sql SELECT * FROM (source_table) UNPIVOT (new_column_name FOR column_to_unpivot IN (list_of_columns)); ``` #### 示例 1：使用 `UNPIVOT` 函数 假设有一个表 `student_scores`，其中包含学生的成绩信息，如下所示： | student_id | math_score | english_score | science_score | |------------|------------|---------------|---------------| | 1 | 85 | 90 | 78 | | 2 | 92 | 88 | 85 | 如果需要将学科分数从列转换为行，则可以使用以下 SQL 查询： ```sql SELECT student_id, subject, score FROM student_scores UNPIVOT ( score FOR subject IN (math_score AS 'Math', english_score AS 'English', science_score AS 'Science') ); ``` 结果如下： | student_id | subject | score | |------------|---------|-------| | 1 | Math | 85 | | 1 | English | 90 | | 1 | Science | 78 | | 2 | Math | 92 | | 2 | English | 88 | | 2 | Science | 85 | 此查询通过 `UNPIVOT` 将 `math_score`, `english_score`, 和 `science_score` 列转换为 `subject` 和 `score` 两列[^1]。 --- #### 示例 2：逗号分隔列转行 如果列中的数据是以逗号分隔的形式存储，例如权限编号字段 `permissions` 中存储了多个权限编号，如 `'1,2,3'`，则可以通过以下方法将其拆分为多行。 假设有一个表 `user_permissions`，结构如下： | user_id | permissions | |---------|-------------| | 1 | 1,2,3 | | 2 | 4,5 | 可以使用以下 SQL 查询将其拆分为多行： ```sql SELECT user_id, TRIM(REGEXP_SUBSTR(permissions, '[^,]+', 1, LEVEL)) AS permission FROM user_permissions CONNECT BY LEVEL <= REGEXP_COUNT(permissions, ',') + 1 AND PRIOR user_id = user_id AND PRIOR SYS_GUID() IS NOT NULL; ``` 结果如下： | user_id | permission | |---------|------------| | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 4 | | 2 | 5 | 此查询通过正则表达式和 `CONNECT BY` 实现了逗号分隔列的拆分[^3]。 --- #### 示例 3：动态列转行 如果需要根据实际数据动态生成列转行的结果，可以结合 `UNPIVOT` 和动态 SQL 来实现。例如，假设有一张表 `dynamic_data`，其结构未知，但需要将所有非空列的数据转换为行。 表结构如下： | id | col_a | col_b | col_c | |----|-------|-------|-------| | 1 | X | Y | Z | | 2 | A | B | C | 查询如下： ```sql SELECT id, column_name, value FROM dynamic_data UNPIVOT ( value FOR column_name IN (col_a, col_b, col_c) ); ``` 结果如下： | id | column_name | value | |----|-------------|-------| | 1 | col_a | X | | 1 | col_b | Y | | 1 | col_c | Z | | 2 | col_a | A | | 2 | col_b | B | | 2 | col_c | C | 此查询通过 `UNPIVOT` 动态地将列名和值转换为行[^4]。 --- ### 注意事项 - 如果需要对结果进行排序或过滤，可以在查询中添加 `ORDER BY` 或 `WHERE` 子句。 - 对于大规模数据集，建议优化查询性能，例如通过索引或分区来加速处理。