行转列、列转行

简介:
一:行转列


模式: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  ,如需转载请自行联系原作者


相关文章
|
2月前
|
关系型数据库 MySQL 数据库
MySQL查询聚合函数与分组查询
MySQL查询聚合函数与分组查询
|
9月前
|
SQL 存储 Oracle
通过sql做数据透视表,数据库表行列转换(pivot和Unpivot用法)(一)
在mssql中大家都知道可以使用pivot来统计数据,实现像excel的透视表功能 一、MSsqlserver中我们通常的用法
276 0
|
存储 关系型数据库 MySQL
多对一!分组查询!MySQL分组函数,聚合函数,分组查询
多对一!分组查询!MySQL分组函数,聚合函数,分组查询
102 0
多对一!分组查询!MySQL分组函数,聚合函数,分组查询
|
SQL
SQL中行转列、列转行
SQL中行转列、列转行
202 0
SQL中行转列、列转行
|
SQL
SQL中的行转列和列转行
SQL是IT行业很多岗位都要求具备的一项能力,对于数据岗位而言更是如此,甚至说扎实的SQL基础也往往是入职这些岗位的必备技能。而在SQL面试中,一道出镜频率很高的题目就是行转列和列转行的问题,可以说这也是一道经典的SQL题目,本文就这一问题做以介绍分享。
778 0
SQL中的行转列和列转行
|
分布式计算 MaxCompute
MaxCompute 行转列 列转行
搜了一下行转列、列转行,除了隐林一篇之外,好像没有了 最近在帮助项目组初学者整理初学者文档,刚好用关系型数据库的例子变化实现了一个 借鉴:https://developer.aliyun.com/article/40518 供大家参考
3746 0
|
SQL HIVE
Hive之行转列/列转行
1、行转列 场景:在hive表中,一个用户会有多个人群标签,List格式(逗号分隔如要转成List),有时我们需要统计一个人群标签下有少用户,这是就需要使用行转列了 例如,user_crowd_info有如下数据 visit_id crowds ...
3133 0
|
关系型数据库 Oracle