oracle 行转列

1.首先建表如下：

create table TEST_LINE_TO_COL(ID NUMBER,NAME NVARCHAR2(50),COURSE NVARCHAR2(20),SCORE  NUMBER);

insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (1, '张三', '语文', 65);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (2, '张三', '数学', 85);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (3, '张三', '英语', 95);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (4, '李四', '语文', 85);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (5, '李四', '数学', 95);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (6, '李四', '英语', 75);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (7, '王五', '语文', 55);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (8, '王五', '数学', 90);
insert into TEST_LINE_TO_COL (ID, NAME, COURSE, SCORE)
values (9, '王五', '英语', 75);

2.11g版本前转换语句如下：

select name,
       sum(decode(course, '语文', score)),
       sum(decode(course, '数学', score)),
       sum(decode(course, '英语', score))
  from TEST_LINE_TO_COL
 group by name;

 

3.11g版本新增pivot(行转列),unpivot(列转行)函数

with t as
 (select name, course, score from test_line_to_col)
select * from t pivot (sum(score) for course in('语文', '数学', '英语'));

顺便在这里说下unpivot的用法：

create table a  
select name,
       sum(decode(course, '语文', score)) 语文,
       sum(decode(course, '数学', score)) 数学,
       sum(decode(course, '英语', score)) 英语
  from TEST_LINE_TO_COL
 group by name;
 select * from a;

结果集如下：

用unpivot，把列转化为行

 select *
   from (select name, course, score
           from a unpivot(score for course in(语文, 数学, 英语)));

 

 注意：unpivot(score for course in(语文,数学,英语))中，语文等不能用单引号；score,course是我们新命名的列，也可以是其它，如val,col,所以select name,后自然要跟我们新的列名。

 

4.Listagg函数，在11gR2版本中引入

Listagg函数在进行字符串处理时很用，这个分析函数提供了将来自多个行中的列值转化为列表格式的能力。

 

语法：Listagg(string,separator) within group(order-by-clause) over(partition-by-clause)

 

具体用法：

select name, listagg(score, ',') within group(order by score)
  from TEST_LINE_TO_COL
 group by name;

 

 

 

 

将10小时内的数据进行转列

select user_id,
       wenxin_id,
       listagg(auction_name, ',') within group(order by auction_name) auction_ids
  from (select *
          FROM auction_reminds
         where ceil(((end_at - sysdate) * 24)) <= 10
           and is_remind = '0')
 group by user_id, wenxin_id;