Kettle实现行转列1(场景)
来源:互联网 发布:耽美网络剧 编辑:程序博客网 时间:2024/06/17 08:34
1.固定列数
- create table t1
- (
- studentno int,
- subject varchar2(10),
- grade int
- );
- insert into t1 values(1,'语文',80);
- insert into t1 values(1,'数学',82);
- insert into t1 values(1,'英语',84);
- insert into t1 values(2,'语文',70);
- insert into t1 values(2,'数学',74);
- insert into t1 values(2,'英语',76);
- insert into t1 values(3,'语文',90);
- insert into t1 values(3,'数学',93);
- insert into t1 values(3,'英语',94);
- commit;
- SQL> select * from t1;
STUDENTNO SUBJECT GRADE
---------- ---------- ----------
1 语文 80
1 数学 82
1 英语 84
2 语文 70
2 数学 74
2 英语 76
3 语文 90
3 数学 93
3 英语 94
已选择9行。 - select studentno 学号,
sum(decode(subject,'语文',grade,null)) 语文,
sum(decode(subject,'数学',grade,null)) 数学,
sum(decode(subject,'英语',grade,null)) 英语
from t1 group by studentno;
学号 语文 数学 英语 - ----- ---------- ---------- ----------
1 80 82 84
2 70 74 76
3 90 93 94使用相关子查询的方式: - select studentno,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='语文') 语文,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='数学') 数学,
(select grade from t1 v2 where v2.studentno=t1.studentno and v2.subject='英语') 英语
from t1 group by studentno;
- create table t2
- (
- key int,
- value varchar2(10)
- );
- insert into t2 values(1,'我');
- insert into t2 values(1,'是');
- insert into t2 values(1,'谁');
- insert into t2 values(2,'知');
- insert into t2 values(2,'道');
- insert into t2 values(3,'不');
- commit;
- SQL> select * from t2;
- KEY VALUE
- ---------- ----------
- 1 我
- 1 是
- 1 谁
- 2 知
- 2 道
- 3 不
- 已选择6行。
- with v1 as
(
select key,value,row_number() over(partition by key order by key) r from t2
),
v2 as
(
select max(sys_connect_by_path(value,' ')) result from v1 start with r=1 connect by r=prior r+1 and key=prior key group by key
)
select * from v2;
RESULT
------------------
我 是 谁
知 道
不
- with v1 as
- (
- select key,value,row_number() over(partition by key order by key) r from t2
- ),
- v2 as
- (
- select listagg(value,',') within group (order by r) result from v1 group by key
- )
- select * from v2;
阅读全文
0 0
- Kettle实现行转列1(场景)
- Kettle实现行转列2(Kettle实现)
- kettle使用常见场景
- kettle入门(1)
- kettle学习日记(1)
- kettle行转列
- Kettle (1)
- Kettle日志之Kettle step materic日志代码实现(一)
- kettle 循环实现方式
- kettle实现循环
- Kettle实现数据库迁移
- kettle实现数据转换
- Kettle调度实现
- java实现kettle
- Kettle定时任务(V4.1)
- Kettle相关文章索引(1)
- Kettle(Pentaho DataIntegration)实现Hadoop-2.2.0 文件复制
- Kettle取代sqoop,实现hbase2mysql,mysql2hbase(小试)
- [POJ]3525 半平面交
- TinyPng批量压缩Python脚本
- MySql常用命令和语法以及可视化工具Navicat的使用
- hdu1251统计难题
- HEVC编码结构
- Kettle实现行转列1(场景)
- Windows安装Mysql客户端
- Python自然语言处理工具小结
- springMVC分页显示
- java ---list
- 985因子对难题
- centos6.5防火墙配置
- jrebel插件在myeclipse中的安装与使用
- 【算法】快速排序