Oracle 中行列转换问题总结

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

 

以上是个简单的小结，如果有问题请大神们告知，以便我的学习，非常感谢！