SQL行列转换技巧总结

“SQL行列转换总结.pdf 是一份PDF文档，作者Caizhuoyi总结了关于SQL中的行列转换技巧，包括列转行、行转列、多列转字符串、多行转字符串、字符串转多列以及字符串转多行。文档适用于Oracle数据库的8i、9i、10g及更高版本，并涉及了model子句和正则表达式等高级知识。” 在SQL中，行列转换是数据处理的重要操作，常用于数据分析和报表展示。以下是针对标题和描述中提到的六种转换方法的详细解释： 1. 列转行 列转行通常使用`UNION ALL`或`MODEL`子句实现。例如，创建一个表`t_col_row`，包含ID和三个列c1、c2、c3，然后通过`UNION ALL`将这些列转换为行。如果不需要包含空值的行，可以添加`WHERE COLUMN IS NOT NULL`条件。 ```sql SELECT id, 'c1' AS cn, c1 AS cv FROM t_col_row UNION ALL SELECT id, 'c2' AS cn, c2 AS cv FROM t_col_row UNION ALL SELECT id, 'c3' AS cn, c3 AS cv FROM t_col_row; ``` 在10g及以上版本，可以使用`MODEL`子句进行更复杂的转换。 2. 行转列 行转列较为复杂，可能需要使用`PIVOT`操作。例如，将上面转换后的行数据再转回列，可以使用`PIVOT`。 ```sql SELECT * FROM ( SELECT id, cn, cv FROM t_col_row WHERE cn IN ('c1', 'c2', 'c3') ) PIVOT ( MAX(cv) FOR cn IN ('c1', 'c2', 'c3') ); ``` 3. 多列转换成字符串 多列转换为一个字符串可以使用`LISTAGG`函数（11g及以后版本）或`WM_CONCAT`（旧版本的非标准函数）。 ```sql -- 11g+ SELECT id, LISTAGG(c1 || ',' || c2 || ',' || c3, ',') WITHIN GROUP (ORDER BY id) AS combined FROM t_col_row; -- 旧版本 SELECT id, WM_CONCAT(c1 || ',' || c2 || ',' || c3) AS combined FROM t_col_row; ``` 4. 多行转换成字符串 多行合并为一个字符串，可以使用`XMLAGG`配合`CLOB`类型。 ```sql SELECT id, TO_CHAR(XMLAGG(XMLELEMENT(e, cv, ',')).EXTRACT('//text()'), 'VARCHAR2(4000)') AS combined FROM ( SELECT id, cv FROM t_col_row ORDER BY id ) GROUP BY id; ``` 5. 字符串转换成多列 字符串拆分为多列通常需要使用`REGEXP_SUBSTR`配合正则表达式。 ```sql WITH data AS ( SELECT id, REGEXP_SUBSTR('v11,v21,v31', '[^,]+', 1, level) AS val FROM t_col_row CONNECT BY PRIOR id = id AND level <= LENGTH('v11,v21,v31') - LENGTH(REPLACE('v11,v21,v31', ',', '')) + 1 ) SELECT id, val, 'c' || level AS col_num FROM data; ``` 6. 字符串转换成多行 类似地，字符串拆分为多行可使用`REGEXP_REPLACE`配合`CONNECT BY`。 ```sql WITH data AS ( SELECT id, REGEXP_REPLACE('v11 v21 v31', ' (\S+)', '\n\1') AS str FROM t_col_row ) SELECT id, TRIM(LEADING ' ' FROM REGEXP_SUBSTR(str, '[^ ]+', 1, level)) AS val FROM data CONNECT BY PRIOR id = id AND level <= LENGTH(str) - LENGTH(REPLACE(str, ' ', '')) + 1; ``` 以上就是SQL中行列转换的基本方法，每种转换都有其特定的应用场景和适用范围。在实际应用中，根据数据结构和需求选择合适的方法，有时还需要结合其他函数和子句进行组合操作。对于更复杂的情况，可能需要结合编程语言或者专门的数据处理工具来完成。