注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

行列转换  

2015-08-29 18:58:46|  分类: sql与pl/sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
SQL> create table t1 (sid number,sub varchar2(10),score number); 
Table created
SQL> insert into t1 values (1,'语文',88);
SQL> insert into t1 values (1,'数学',89);
SQL> insert into t1 values (1,'英语',76);
SQL> insert into t1 values (2,'语文',65);
SQL> insert into t1 values (2,'数学',86);
SQL> insert into t1 values (2,'英语',78);
SQL> insert into t1 values (3,'语文',90);
SQL> insert into t1 values (3,'数学',100);
SQL> insert into t1 values (3,'英语',99);
SQL> commit;
SQL> select * from t1; 
       SID SUB             SCORE
---------- ---------- ----------
         1 语文               88
         1 数学               89
         1 英语               76
         2 语文               65
         2 数学               86
         2 英语               78
         3 语文               90
         3 数学              100
         3 英语               99
 
--行转列
SQL> select
  2    sid,
  3    max(case sub when '语文' then score else null end) CHINESE,
  4    max(case sub when '数学' then score else null end) MATH,
  5    max(case sub when '英语' then score else null end) ENGLISH
  6  from t1
  7  group by sid;
 
       SID    CHINESE       MATH    ENGLISH
---------- ---------- ---------- ----------
         1         88         89         76
         2         65         86         78
         3         90        100         99
SQL> create table t2 as
  2  select
  3    sid,
  4    max(case sub when '语文' then score else null end) CHINESE,
  5    max(case sub when '数学' then score else null end) MATH,
  6    max(case sub when '英语' then score else null end) ENGLISH
  7  from t1
  8  group by sid;
 
SQL> select * from t2; 
       SID    CHINESE       MATH    ENGLISH
---------- ---------- ---------- ----------
         1         88         89         76
         2         65         86         78
         3         90        100         99
SQL> truncate table t1;
 
SQL> insert all
  2  into t1 values(sid,'语文',chinese)
  3  into t1 values(sid,'数学',math)
  4  into t1 values(sid,'英语',english)
  5  select * from t2;
 
SQL> commit;
 
SQL> select * from t1 order by 1; 
       SID SUB             SCORE
---------- ---------- ----------
         1 数学               89
         1 语文               88
         1 英语               76
         2 语文               65
         2 数学               86
         2 英语               78
         3 语文               90
         3 数学              100
         3 英语               99
 
 --11g新方法
--行转列
SQL> select * from t1 pivot(max(score) for sub in ('语文' chinese,'数学' math,'英语' english));
 
       SID    CHINESE       MATH    ENGLISH
---------- ---------- ---------- ----------
         1         88         89         76
         2         65         86         78
         3         90        100         99
 --列转行
SQL> select * from t2 unpivot(score for sub in (chinese as '语文',math as '数学',english as '英语'));
 
       SID SUB         SCORE
---------- ------ ----------
         1 语文           88
         1 数学           89
         1 英语           76
         2 语文           65
         2 数学           86
         2 英语           78
         3 语文           90
         3 数学          100
         3 英语           99
 
9 rows selected
SQL> --行转串
SQL> select sid, vmsys.vm_concat(sub) sub from t1 group by sid;  --很多版本不支持了 
ORA-00904: "VMSYS"."VM_CONCAT": 标识符无效
SQL> select substr(max(SYS_CONNECT_BY_PATH(sub, ',')),2) as sub
  2   from (select sid, sub , rownum as rn from t1)
  3   start with rn = 1
  4   connect by rn - 1 = prior rn; 
SUB
--------------------------------------------------------------------------------
语文,语文,语文,数学,数学,数学,英语,英语,英语
SQL> select sid, listagg(sub,',') within group (order by sub desc) sub from t1 group by sid; 
       SID SUB
---------- --------------------------------------------------------------------------------
         1 语文,英语,数学
         2 语文,英语,数学
         3 语文,英语,数学
SQL> create table t3 as select sid, listagg(sub,',') within group (order by sub desc) sub from t1 group by sid;
 
SQL> select * from t3; 
       SID SUB
---------- --------------------------------------------------------------------------------
         1 语文,英语,数学
         2 语文,英语,数学
         3 语文,英语,数学
SQL> select sid, regexp_substr(sub, '[^,]+', 1, 1) as sub from t3
  2  union all
  3  select sid, regexp_substr(sub, '[^,]+', 3, 1) as sub from t3
  4  union all
  5  select sid, regexp_substr(sub, '[^,]+', 3, 1) as sub from t3; 
       SID SUB
---------- --------------------------------------------------------------------------------
         1 语文
         2 语文
         3 语文
         1 英语
         2 英语
         3 英语
         1 英语
         2 英语
         3 英语
 
SQL> select sid, sub from(
  2  select sid, 1 as p ,rtrim(regexp_substr(sub||',', '.&?' || ',', 1,1),',') as sub from t3
  3  union all
  4  select sid, 2 as p ,rtrim(regexp_substr(sub||',', '.&?' || ',', 1,2),',') as sub from t3
  5  union all
  6  select sid, 3 as p ,rtrim(regexp_substr(sub||',', '.&?' || ',', 1,3),',') as sub from t3
  7  /*union all
  8  select sid, 4 as p ,rtrim(regexp_substr(sub||',', '.&?' || ',', 1,4),',') as sub from t1*/)
  9  where sub is not null; 
       SID SUB
---------- --------------------------------------------------------------------------------
         1 文
         2 文
         3 文
         1 语
         2 语
         3 语
         1 学
         2 学
         3 学
 

  评论这张
 
阅读(87)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018