开发者社区> 技术小美> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

行转列、列转行

简介:
+关注继续查看
一:行转列


模式:N行转一列  本实验N取3
1.建表
SQL> create table grade(id int,name varchar2(10),subject varchar2(10),grade number(3));
2.插入实验数据
insert into grade values(1,'Andy','Yuwen',11);
insert into grade values(2,'Andy','Math',22);
insert into grade values(3,'Andy','English',33);
insert into grade values(4,'Zhang','English',44);
insert into grade values(5,'Zhang','Math',55);
insert into grade values(6,'Zhang','Yuwen',66);
insert into grade values(7,'Tao','Yuwen',77);
insert into grade values(8,'Tao','Math',88);
commit;
3.查询
SQL> select * from grade;


        ID NAME       SUBJECT         GRADE
---------- ---------- ---------- ----------
         1 Andy       Yuwen              11
         2 Andy       Math               22
         3 Andy       English            33
         4 Zhang      English            44
         5 Zhang      Math               55
         6 Zhang      Yuwen              66
         7 Tao        Yuwen              77
         8 Tao        Math               88
8 rows selected.
4.错误示范,缺失 sum 。
SQL>  
select name,
decode(subject,'Yuwen', grade,null) "Language",
decode(subject,'Math', grade,null) "Math",
decode(subject,'English', grade,null) "English"
from grade;


NAME         Language       Math    English
---------- ---------- ---------- ----------
Andy               11
Andy                          22
Andy                                     33
Zhang                                    44
Zhang                         55
Zhang              66
Tao                77
Tao                           88


4. 转换。正确示范
方法一:
SQL> 
select name,
sum(decode(subject,'Yuwen', grade,0)) "Yuwen",
sum(decode(subject,'Math', grade,0)) "Math",
sum(decode(subject,'English', grade,0)) "English"
from grade
group by name;


NAME            Yuwen       Math    English
---------- ---------- ---------- ----------
Zhang              66         55         44
Andy               11         22         33
Tao                77         88          0
方法二:
SELECT name,
SUM(CASE WHEN subject='Yuwen' THEN grade ELSE 0 END) AS "Yuwen",
SUM(CASE WHEN subject='Math' THEN grade ELSE 0 END) AS "Math",
SUM(CASE WHEN subject='English' THEN grade ELSE 0 END) AS "English"
FROM grade
group by name;


二、列转行


模式:一列转N行,本实验N取3 (说明:相同类型数据确定N的取值,比如:YUWEN,MATH,ENGLISH 所以取3)
1.建表
create table score(id int,name varchar2(10),Yuwen number(3),Math number(3),English number(3));
2.插入数据
insert into score values(1,'Zhang',11,22,33);
insert into score values(2,'Andy',44,55,66);
insert into score(id,name,Yuwen) values(3,'Tao',77);
commit;
3.查询
SQL> select * from score;


        ID NAME            YUWEN       MATH    ENGLISH
---------- ---------- ---------- ---------- ----------
         1 Zhang              11         22         33
         2 Andy               44         55         66
         3 Tao                77
4.转换
SELECT name, 'Yuwen' subject,Yuwen grade FROM score
UNION ALL
SELECT name, 'Math' subject,Math grade FROM score
UNION ALL
SELECT name, 'English' subject,English grade FROM score
order by name;


NAME       SUBJECT      GRADE
---------- ------- ----------
Andy       Math            55
Andy       English         66
Andy       Yuwen           44
Tao        English
Tao        Math
Tao        Yuwen           77
Zhang      Math            22
Zhang      English         33
Zhang      Yuwen           11
9 rows selected.
注意:加引号表示是字符串,不加引号取列的值
模式:N列转成一行
1.建表
create table test(dname varchar2(10),ename varchar2(10));
2.插入数据
insert into test values('Guowang','Andy');
insert into test values('Guowang','Zhang');
insert into test values('Guowang','Chong');
insert into test values('Nvwang','Tao');
insert into test values('Nvwang','Ye');
insert into test values('happy','Leaf');
commit;
3.查询
SQL> select * from test;


DNAME      ENAME
---------- ----------
Guowang    Andy
Guowang    Zhang
Guowang    Chong
Nvwang     Tao
Nvwang     Ye
happy      Leaf
6 rows selected.
目标转换模式:
DNAME      NAME
---------- ----------------
Guowang    Andy,Zhang,Chong
Nvwang     Tao,Ye
happy      Leaf
4. 转换
方法一:
-- 一种简单通过函数的方法,有oracle版本限制,作为一种备选方案。
SQL> col name for a40
SQL> select dname,WMSYS.WM_CONCAT(ename) NAME from test group by dname;


DNAME      NAME
---------- ----------------------------------------
Guowang    Andy,Chong,Zhang
Nvwang     Tao,Ye
happy      Leaf
方法二:
构造出来一个层次,利用层次查询处理
4.2.1 -- 虚构员工编号
SQL> SELECT dname, ename, ROW_NUMBER() OVER (ORDER BY dname) rn FROM test;


DNAME      ENAME              RN
---------- ---------- ----------
Guowang    Andy                1
Guowang    Zhang               2
Guowang    Chong               3
Nvwang     Tao                 4
Nvwang     Ye                  5
happy      Leaf                6
4.2.2-- 领导编号
SELECT dname, ename, rn, LEAD (rn) OVER (PARTITION BY dname ORDER BY rn) rn1
FROM (SELECT dname, ename, ROW_NUMBER () OVER (ORDER BY dname) rn FROM test);
DNAME      ENAME              RN        RN1
---------- ---------- ---------- ----------
Guowang    Andy                1          2
Guowang    Zhang               2          3
Guowang    Chong               3
Nvwang     Tao                 4          5
Nvwang     Ye                  5
happy      Leaf                6
4.2.3 转换
col name for a40;
SELECT dname, SUBSTR(MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) NAME
FROM (
SELECT dname, ename, rn, LEAD (rn) OVER (PARTITION BY dname ORDER BY rn) rn1
FROM (SELECT dname, ename, ROW_NUMBER () OVER (ORDER BY ename) rn
FROM test)
)
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY dname;


DNAME      NAME
---------- -----------------------------
happy      Leaf
Nvwang     Ye,Tao

Guowang    Zhang,Chong,Andy



本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/6798650.html  ,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Hive之行转列/列转行
1、行转列 场景:在hive表中,一个用户会有多个人群标签,List格式(逗号分隔如要转成List),有时我们需要统计一个人群标签下有少用户,这是就需要使用行转列了 例如,user_crowd_info有如下数据 visit_id crowds ...
2611 0
图标字体化浅谈[转]
在做手机端Web App项目中,经常会遇到小图标在手机上显示比较模糊的问题,经过实践发现了一种比较好的解决方案,图标字体化。在微社区项目中,有很多小的Icon(图 标),如分享、回复、赞、返回、话题、访问、箭头等,这些Icon(图标)一般都是纯色的。开始制作时考虑用双倍大小的Sprite图,通
2040 0
SQL行转列
目的:将相同条件的多行值合并到同一列, 1.创建测试表: CREATE TABLE [dbo].[TB_01]( [SN] [nvarchar](50) NULL, [STEP_NAME] [nvarchar](50) NULL, [SDATE] [datetime] NULL ) ON [PRIMARY] 2.
663 0
SQL行转列汇总
原文:SQL行转列汇总 PIVOT 用于将列值旋转为列名(即行转列),在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是:PIVOT(聚合函数(列) FOR 列 in (…) )AS P 注意:PIVOT、UNPIVOT是SQL Server 2005...
988 0
T-SQL行转列、列转行
这是原表: 想实现这种效果: 过程我就不写了,把原代码贴给大家: 1 select (select qhmc from tab_qh c where c.qhid=b.sjqhbh) sjqh ,qhmc,a.
691 0
[转载]SQL行转列,数据统计
SELECT          CreateTime as '日期',          isnull( [支付宝],0) as '支付宝' ,          isnull( [手机短信],0) as '手机短信',         isnull([工商银行卡],0)as '工商银行'...
638 0
+关注
6819
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载