Oracle行转列/行变列

在写一些复杂的SQL的时候，经常需要使用到行变列的技巧，一些帐务系统的统计也经常会写到这样的SQL。主要用到了max、decode或nvl这些函数来达到目的。写下来让大家互相学习学习！下面是一个行变列的例子。

如表ROW2COLUMN2有以下数据：

名字 课程 分数

1 张三 语文 80
2 张三 数学 86
3 张三 英语 75
4 李四 语文 78
5 李四 数学 85
6 李四 英语 78

想变成下面记录：

名字 语文 数学 英语

1 李四 78 85 78
2 张三 80 86 75

----------------------------------------------

CREATE TABLE ROW2COLUMN2(NAME_ VARCHAR2(20),
CLASS VARCHAR2(50),
SCORE NUMBER(3));

INSERT INTO ROW2COLUMN2 VALUES ('张三', '语文', 80);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '数学', 86);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '英语', 75);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '语文', 78);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '数学', 85);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '英语', 78);

SELECT * FROM ROW2COLUMN2;

SELECT NAME_,
MAX(DECODE(CLASS, '语文', T.SCORE, 0)) 语文,
MAX(DECODE(CLASS, '数学', T.SCORE, 0)) 数学,
MAX(DECODE(CLASS, '英语', T.SCORE, 0)) 英语
FROM ROW2COLUMN2 T
GROUP BY NAME_;

评论

2 楼 zhengyong7232 2012-11-29  

表test
NAME
KM
CJ
张三
语文
80
张三
数学
86
张三
英语
75
李四
语文
78
李四
数学
85
李四
英语
79
表test2
NAME
语文
数学
英语
张三
80
86
75
李四
78
85
79
行转列sql ------test->test2
SELECT NAME,
       SUM(decode(t.km, '语文', t.cj, 0)) 语文,
       SUM(decode(t.km, '数学', t.cj, 0)) 数学,
       SUM(decode(t.km, '英语', t.cj, 0)) 英语
  FROM test t
GROUP BY t.name
列转行sql ------test2->test
select t.name,'语文' AS km,t.语文 AS cj from test2 t
UNION ALL
select t.name,'数学' AS km,t.数学 AS cj from test2 t
UNION ALL
select t.name,'英语' AS km,t.英语 AS cj from test2 t
ORDER BY name

1 楼 zhengyong7232 2012-11-28  

create table test_tab(province varchar2(20),landscapes varchar2(50));
insert into test_tab values('北京','故宫');
insert into test_tab values('北京','颐和园');
insert into test_tab values('北京','长城');
insert into test_tab values('上海','外滩');
insert into test_tab values('上海','浦东');
insert into test_tab values('上海','世博园');
insert into test_tab values('四川','蜀南竹海');
SELECT * FROM test_tab;

SELECT t.province,
DECODE(landscapes, '故宫', T.landscapes, '' ) a1,
DECODE(landscapes, '颐和园', T.landscapes, '')  a2,
DECODE(landscapes, '长城', T.landscapes,'' ) a3
FROM test_tab T

SELECT t.province,
max(DECODE(landscapes, '故宫', T.landscapes, '')) a1,
max(DECODE(landscapes, '颐和园', T.landscapes, '')) a2,
max(DECODE(landscapes, '长城', T.landscapes,'')) a3
FROM test_tab T
GROUP BY t.province


select a.province,a.a1||','||a.a2||a.a3 as aa from
(SELECT t.province,
max(DECODE(landscapes, '故宫', T.landscapes, '')) a1,
max(DECODE(landscapes, '颐和园', T.landscapes, '')) a2,
max(DECODE(landscapes, '长城', T.landscapes,'')) a3
FROM test_tab T
GROUP BY t.province) a