1)行转列

表结构:STUDENT

create table STUDENT
( USER_NAME VARCHAR2(20),
  COURSE VARCHAR2(20),
  SCORE FLOAT )

初始化数据:

INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Enlish',85); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Math',75); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Chinese',97); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Physics',76); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Enlish',95); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Math',67); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Physics',89); INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Chinese',79);

初始数据如下图：

如果需要实现如下的查询效果图：

这就是最常见的行转列，主要原理是利用decode函数、聚集函数(MAX)，结合group by分组实现的，MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。

SQL代码如下:

SELECT t.user_name, MAX(decode(course,'English',score,0)) AS English, MAX(decode(course,'Math',score,0)) AS Math, MAX(decode(course,'Chinese',score,0)) AS Chinese, MAX(decode(course,'Physics',score,0)) AS Physics FROM student t GROUP BY t.user_name

或者你不是用decode函数

SELECT t.user_name, MAX(case course when 'English' then score else 0 end) AS English, MAX(case course when 'Math' then score else 0 end) AS Math, MAX(case course when 'Chinese' then score else 0 end) AS Chinese, MAX(case course when 'Physics' then score else 0 end) AS Physics FROM student t GROUP BY t.user_name

2)多行转换成字符串

表结构:tb_name

create table tb_name(id int, remark varchar(2))

初始化数据:

INSERT INTO tb_name VALUES(1,'a'); INSERT INTO tb_name VALUES(1,'b'); INSERT INTO tb_name VALUES(1,'c'); INSERT INTO tb_name VALUES(2,'a'); INSERT INTO tb_name VALUES(2,'d'); INSERT INTO tb_name VALUES(2,'e'); INSERT INTO tb_name VALUES(3,'c'); COMMIT;

初始数据如下图：

如果需要实现如下的查询效果图

wm_concat是oracle 10g引入，以逗号分隔连接列的值

SQL如下:

select id,wm_concat(remark) remark from tb_name t group by id

3)列转行

表结构:

create table TB_TEST   
( USER_NAME VARCHAR2(20),   
  English FLOAT,   
  Math FLOAT,   
  Chinese FLOAT,
  Physics FLOAT )

初始化数据:

insert into TB_TEST (USER_NAME, ENGLISH, MATH, CHINESE, PHYSICS) values ('zhangyue', 85, 75, 97, 76); insert into TB_TEST (USER_NAME, ENGLISH, MATH, CHINESE, PHYSICS) values ('heshan', 95, 67, 79, 89); COMMIT;

初始数据如下图：

如果需要实现如下的查询效果图

我们这里要使用UNION函数请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。

同时，每条 SELECT 语句中的列的顺序必须相同。

SQL语句:

select user_name,'Enlish' course,ENGLISH score from tb_test t UNION select user_name,'Math' course,MATH score from tb_test t UNION select user_name,'Chinese' course,CHINESE score from tb_test t UNION select user_name,'Physics' course,PHYSICS score from tb_test t

4）多列转换成字符串

表结构:

create table TB_COL
(
  ID INTEGER,
  C1 VARCHAR2(2),
  C2 VARCHAR2(2),
  C3 VARCHAR2(2)
)

初始化数据:

insert into tb_col (ID, C1, C2, C3) values (1, 'c1', 'c2', 'c3'); insert into tb_col (ID, C1, C2, C3) values (2, 'c4', 'c5', 'c6'); insert into tb_col (ID, C1, C2, C3) values (3, 'c7', 'c8', 'c9'); COMMIT;

初始数据如下图：

如果需要实现如下的查询效果图

这个比较简单，用||或concat函数可以实现：

SQL语句如下:

select id,c1||','||c2||','||c3 as col from tb_col t

	
		Pivot语法：
		

		
			
			
				 
				
					
				
 
			

		

		
			
			
				SELECT ....   
			

			
				FROM <table-expr>   
			

			
				   PIVOT   
			

			
				     (   
			

			
				      aggregate-function(<column>)   
			

			
				      FOR column> IN (, ,..., )   
			

			
				        ) AS    
			

			
				WHERE .....   
			

		

			
unprivot

	UNPIVOT INCLUDE NULLS 指定空值也进行转置，如果是EXCLUDE NULLS 将忽略空值。

	
			
				SELECT ....   
			

			
				FROM <table-expr>   
			

			
				   UNPIVOT   
			

			
				     (   
			

			
				      aggregate-function(<column>)   
			

			
				      FOR column> IN (, ,..., )   
			

			
				        ) AS    
			

			
				WHERE .....

Oracle 中行列转换问题总结