精华内容
下载资源
问答
  • 数据库函数 行转列转行 原理实现分析以及基础语法实现
    2021-01-19 05:07:49

    行转列与列转行的操作,在数据查询中还是用得比较多的。可以将列名转换为列值,也可以将列值转换为列名。使相同的数据以多姿态的形式呈现出来,让用户能更直观的感受数据。

    行转列:简单表演示

    先来看看效果:

    这是原表的数据: select * from city3 order by nation,ranking;

    我们将“ranking”列作为列名,“city”列作为列值,开始行转列操作:

    语法格式: TABLE_SOURCE

    PIVOT(

    聚合函数(value_column)

    FOR pivot_column

    IN()

    )

    具体实现:select * from city3 pivot (max(city) for ranking in ('第一','第二','第三','第四'));

    这样是不是感觉清晰了很多。但这样写只能用于本次查看。如果此时你将这个结果集作为一个内建视图,用于另外的查询。或者直接将这个查询结果新建为一个表,那么就会有新的问题产生。

    注意看结果集的列名,'第一' '第二' 。这些列名是包含在“单引号”中的,你在查询时,是无法使用这样的列名的。因为你这么写,数据库会把它当成一个字符串。然后无法匹配你的列,是不是很神奇?

    那我们要怎么做呢?我们可以在行转列的时候,为生成的列指定“列别名”。和普通列别名一样,使用as关键字指定即可。

    select * from city3 pivot (max(city) for ranking in ('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四));

    多列表演示

    下面我们来看看多列的: select * from city order by nation,num;

    采用行转列后:

    select * from city pivot (max(city) for ranking in ('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四));

    是不是很神奇?看起来有点晕了,其实我们仔细分析就能看出其中的原理:

    1.行转列操作的数据只能是2列,一列作为列名,一列作为列值。

    2.其他的列都会进行分组操作。有点像group by nation,num;

    知道这2个规律了,我们就可以来进行行转列的基础语法实现了。

    行转列基础语法实现

    分析:其实就是分组函数加上内容选择。

    基础语法实现:

    select nation,num,

    max(case ranking when '第一' then city else null end) as 第一,

    max(case ranking when '第二' then city else null end) as 第二,

    max(case ranking when '第三' then city else null end) as 第三,

    max(case ranking when '第四' then city else null end) as 第四

    from city group by nation,num;

    我们可以看到,第一,第二,第三,第四几列,都使用了内容选择,并且因为语句使用了group by字句。所以内容选择必须嵌套在组函数中。这也解释了,为什么函数语法中,需要我们加上组函数了。

    列转行:简单表演示

    列转行,其实就是行专列的逆推导。

    这是原表的数据: select * from city4;

    我们将列名转换为列值,开始列转行操作

    语法格式: TABLE_SOURCE

    UNPIVOT(

    value_column

    FOR pivot_column

    IN()

    )

    具体实现:select * from city4 unpivot (city_name for rank_num in(第一,第二,第三,第四));

    注意:这里的列名是匹配具体的列,不能加单引号。

    如果之前列转行时,我们没有指定不带单引号的列别名的话,我们将无法完成逆推导。

    这样就将数据还原了回来,生成的列名,其实就是我们参数中给定的名字。

    多列表演示

    下面我们来看看多列的: select * from city2;

    采用列转行后:

    select * from city2 unpivot (city_name for rank_num in(第一,第二,第三,第四));

    数据同样还原了回来,下面我们来看看基础语法的实现。

    列转行基础语法实现

    分析:分组函数的逆推导,我们可以查询单个“组”,然后用union来连接结果。

    基础语法实现:

    select nation,num,'第一' as rank_num,第一 as city_name from city2 where 第一 is not null union

    select nation,num,'第二' as rank_num,第二 as city_name from city2 where 第二 is not null union

    select nation,num,'第三' as rank_num,第三 as city_name from city2 where 第三 is not null union

    select nation,num,'第四' as rank_num,第四 as city_name from city2 where 第四 is not null;

    上面我们需要注意,在多列表中,列值可能存在空值,所以我们需要在where字句中,将空值进行过滤。

    更多相关内容
  • [一]、行转列1.1、初始测试数据表结构:TEST_TB_GRADEcreatetableTEST_TB_GRADE(IDNUMBER(10)notnull,USER_NAMEVARCHAR2(20CHAR),COURSEVARCHAR2(20CHAR),SCOREFLOAT)createtableTEST_TB_GRADE(IDNUMBER(10)n...

    [一]、行转列

    1.1、初始测试数据

    表结构:TEST_TB_GRADE

    create

    table TEST_TB_GRADE

    (

    ID        NUMBER(10)notnull,

    USER_NAME VARCHAR2(20CHAR),

    COURSE    VARCHAR2(20CHAR),

    SCOREFLOAT

    )

    createtableTEST_TB_GRADE

    (

    ID        NUMBER(10) notnull,

    USER_NAME VARCHAR2(20 CHAR),

    COURSE    VARCHAR2(20 CHAR),

    SCORE     FLOAT

    )

    初始数据如下图:

    0818b9ca8b590ca3270a3433284dd417.png

    1.2、 如果需要实现如下的查询效果图:

    0818b9ca8b590ca3270a3433284dd417.png

    这就是最常见的行转列,主要原理是利用decode函数、聚集函数(sum),结合group by分组实现的,具体的sql如下:

    select t.user_name,

    sum(decode(t.course,'语文', score,null))asCHINESE,

    sum(decode(t.course,'数学', score,null))asMATH,

    sum(decode(t.course,'英语', score,null))asENGLISH

    fromtest_tb_grade t

    groupbyt.user_name

    orderbyt.user_name

    selectt.user_name,

    sum(decode(t.course,'语文', score,null))asCHINESE,

    sum(decode(t.course,'数学', score,null))asMATH,

    sum(decode(t.course,'英语', score,null))asENGLISH

    fromtest_tb_grade t

    groupbyt.user_name

    orderbyt.user_name

    1.3、延伸

    如果要实现对各门功课的不同分数段进行统计,效果图如下:

    0818b9ca8b590ca3270a3433284dd417.png

    具体的实现sql如下:

    select t2.SCORE_GP,

    sum(decode(t2.course,'语文', COUNTNUM,null))asCHINESE,

    sum(decode(t2.course,'数学', COUNTNUM,null))asMATH,

    sum(decode(t2.course,'英语', COUNTNUM,null))asENGLISH

    from(

    selectt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'endasSCORE_GP,

    count(t.score)asCOUNTNUM

    FROMtest_tb_grade t

    groupbyt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'end

    orderbyt.course ) t2

    groupbyt2.SCORE_GP

    orderbyt2.SCORE_GP

    selectt2.SCORE_GP,

    sum(decode(t2.course,'语文', COUNTNUM,null))asCHINESE,

    sum(decode(t2.course,'数学', COUNTNUM,null))asMATH,

    sum(decode(t2.course,'英语', COUNTNUM,null))asENGLISH

    from(

    selectt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'endasSCORE_GP,

    count(t.score)asCOUNTNUM

    FROMtest_tb_grade t

    groupbyt.course,

    casewhent.score  <60then'00-60'

    whent.score >=60andt.score <80then'60-80'

    whent.score >=80then'80-100'end

    orderbyt.course ) t2

    groupbyt2.SCORE_GP

    orderbyt2.SCORE_GP

    [二]、列转行

    1.1、初始测试数据

    表结构:TEST_TB_GRADE2

    create

    table TEST_TB_GRADE2

    (

    ID         NUMBER(10)notnull,

    USER_NAME  VARCHAR2(20CHAR),

    CN_SCOREFLOAT,

    MATH_SCOREFLOAT,

    EN_SCOREFLOAT

    )

    createtableTEST_TB_GRADE2

    (

    ID         NUMBER(10) notnull,

    USER_NAME  VARCHAR2(20 CHAR),

    CN_SCORE   FLOAT,

    MATH_SCORE FLOAT,

    EN_SCORE   FLOAT

    )

    初始数据如下图:

    0818b9ca8b590ca3270a3433284dd417.png

    1.2、 如果需要实现如下的查询效果图:

    0818b9ca8b590ca3270a3433284dd417.png

    这就是最常见的列转行,主要原理是利用SQL里面的union,具体的sql语句如下:

    select user_name,

    '语文' COURSE , CN_SCORE

    as SCORE

    from test_tb_grade2

    unionselectuser_name,'数学'COURSE, MATH_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'英语'COURSE, EN_SCOREasSCOREfromtest_tb_grade2

    orderbyuser_name,COURSE

    selectuser_name,'语文'COURSE , CN_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'数学'COURSE, MATH_SCOREasSCOREfromtest_tb_grade2

    unionselectuser_name,'英语'COURSE, EN_SCOREasSCOREfromtest_tb_grade2

    orderbyuser_name,COURSE

    也可以利用【insert all into ... select】来实现,首先需要先建一个表TEST_TB_GRADE3:

    create

    table TEST_TB_GRADE3

    (

    USER_NAME VARCHAR2(20CHAR),

    COURSE    VARCHAR2(20CHAR),

    SCOREFLOAT

    )

    createtableTEST_TB_GRADE3

    (

    USER_NAME VARCHAR2(20 CHAR),

    COURSE    VARCHAR2(20 CHAR),

    SCORE     FLOAT

    )

    再执行下面的sql:

    insert

    all

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'语文', CN_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'数学', MATH_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'英语', EN_SCORE)

    selectuser_name, CN_SCORE, MATH_SCORE, EN_SCOREfromtest_tb_grade2;

    commit;

    insertall

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'语文', CN_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'数学', MATH_SCORE)

    intotest_tb_grade3(USER_NAME,COURSE,SCORE)values(user_name,'英语', EN_SCORE)

    selectuser_name, CN_SCORE, MATH_SCORE, EN_SCOREfromtest_tb_grade2;

    commit;

    别忘记commit操作,然后再查询TEST_TB_GRADE3,发现表中的数据就是列转成行了。

    展开全文
  • mssql 数据库行转列,列转行终极方案,需要的朋友可以参考下。
  • 行转列 将图1做成图2的效果 图1: 图2: 创建数据表: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `USER_NAME` varchar(20) DEFAULT NULL, `COURSE` varchar(20) DEFAULT NULL, `...

    MySql

    行转列
    将图1做成图2的效果
    图1:
    在这里插入图片描述
    图2:
    在这里插入图片描述
    创建数据表:

    CREATE TABLE `TEST_TB_GRADE` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `USER_NAME` varchar(20) DEFAULT NULL,
      `COURSE` varchar(20) DEFAULT NULL,
      `SCORE` float DEFAULT '0',
      PRIMARY KEY (`ID`)
    ) ;
    

    导入数据:

    insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
    ("张三", "数学", 34),
    ("张三", "语文", 58),
    ("张三", "英语", 58),
    ("李四", "数学", 45),
    ("李四", "语文", 87),
    ("李四", "英语", 45),
    ("王五", "数学", 76),
    ("王五", "语文", 34),
    ("王五", "英语", 89);
    

    行转列代码实现:

    select username,
    	max(case course when '数学' then score else 0 end) 数学,
    	max(case course when '语文' then score else 0 end) 语文,
    	max(case course when '英语' then score else 0 end) 英语
    from test_tb_grade
    group by username;
    

    hive

    原效果:
    在这里插入图片描述
    代码实现:

    select country,gender,count(*) from customer_details group by country,gender;
    

    行转列效果:
    方法一、
    在这里插入图片描述
    代码实现:

    select country,
    	sum (case gender when 'Male' then 1 else 0 end) as male,
    	sum (case gender when 'Female' then 1 else 0 end) as female
    from customer_details group by country;
    

    方法二、
    在这里插入图片描述
    代码实现:

    with
    t1 as (select country,size(collect_list(gender)) as male from customer_details where gender='Male' group by country),
    t2 as (select country,size(collect_list(gender)) as Female from customer_details where gender='Female' group by country)
    select t1.country,t1.male,t2.Female from t1 inner join t2 on t1.country=t2.country;
    
    展开全文
  • 数据库行转列

    千次阅读 2019-05-24 11:48:00
    要把数据变成,一目了然的看到一个学生的所有科目成绩,SQL如下: SELECT Name, MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文', MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '...

    话不多说直接进入正题

    数据接库数据如下图:

     要把数据变成行,一目了然的看到一个学生的所有科目成绩,SQL如下:

    SELECT Name, 
    MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
    MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
    MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
    MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
    FROM StudentScores
    GROUP BY Name

    执行结果如下:

    这里解释一下SQL,查询的时候用case when then选择需要进行转行的字段以及字段结果,即当Subject是xx的时候选择Subject对应的Score作为Subject的成绩,这里需要注意case when then的结果要用max函数包裹,不然结果也会变成行,但是每行只有一科的成绩,用max包裹就是选择最大成绩,把多行合并成一行完成行转列。

    再举个栗子吧

    数据库数据如下:

    要求按日期、支付方式来统计充值金额信息,SQL如下:

    SELECT Time, 
    CASE Type WHEN '支付宝'  THEN SUM(Money) ELSE 0 END AS '支付宝', 
    CASE Type WHEN '微信'    THEN SUM(Money) ELSE 0 END AS '微信', 
    CASE Type WHEN '云闪付'  THEN SUM(Money) ELSE 0 END AS '云闪付', 
    CASE Type WHEN '信用卡'  THEN SUM(Money) ELSE 0 END AS '信用卡' 
    FROM game 
    GROUP BY Time, Type

    SQL的执行结果如下:

     这个结果也只是统计出了不同时间,不同类型的充值,虽然行转列了,但是每行只有一个字段有数据,如果需要做统计还需要再进行处理,处理SQL如下:

    SELECT Time, 
    SUM(支付宝) AS 支付宝,
    SUM(微信) AS 微信,
    SUM(云闪付) AS 云闪付,
    SUM(信用卡) AS 信用卡
    FROM 
    (SELECT Time, 
    CASE Type WHEN '支付宝'  THEN SUM(Money) ELSE 0 END AS '支付宝', 
    CASE Type WHEN '微信'    THEN SUM(Money) ELSE 0 END AS '微信', 
    CASE Type WHEN '云闪付'  THEN SUM(Money) ELSE 0 END AS '云闪付', 
    CASE Type WHEN '信用卡'  THEN SUM(Money) ELSE 0 END AS '信用卡' 
    FROM game GROUP BY Time, Type) a
    GROUP BY Time

     同过对第一次SQL的再一次求和,完成了去除0值,结果如下:

     OK!!!

    展开全文
  • 数据库中的行转列 与 列转行

    千次阅读 2020-08-06 11:15:00
    1.创建数据库表,为表字段添加数据 #创建数据库表结构 create table tb_student_grade( id int(10) not null auto_increment primary key, s_name varchar(20) default null, course varchar(20) default null, ...
  • 多行字符串 原数据 结果数据库 通过FOR XML PATH把skill按-拼接在一起(-缠绕-寄生-蛛网束缚),然后使用STUFF函数把第一个-符号去掉。 SQL Code select A.* into #result from ( select '唐三' as ...
  • select * from (select name, nums as "Purchase Frequency"from demo t)pivot xml (sum(nums) for name in (any))如你所见, NAME_XML 是 XMLTYPE,其中根元素是 。每个值以名称-值元素对的形式表示。你可以使用...
  • 数据库行转列与列转行方法

    万次阅读 2018-06-04 23:51:28
    --纵表横表  TABLE_A --> TABLE_B --方法一:使用case ... when ... then ... else ... end 函数 select name,  sum (case subject when '语文' then score else 0 end) as chinese,  sum (case subject when ...
  • 目录结构如下:行转列列转行[一]、行转列1.1、初始测试数据表结构:TEST_TB_GRADEcreatetableTEST_TB_GRADE(IDNUMBER(10)notnull,USER_NAMEVARCHAR2(20CHAR),COURSEVARCHAR2(20CHAR),SCOREFLOAT)createtableTEST_TB_...
  • 行列互,可以分为静态互,即事先就知道要处理多少);动态互,事先不知道处理多少()。 --创建测试环境 USE tempdb; GO IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE ...
  • mysql行转列、列转行  语句不难,不做多余解释了,看语句时,从内往外一句一句剖析 行转列  有如图所示的表,现在希望查询的结果将行转成列  建表语句如下: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT...
  • [数据库]Oracle行转列(使用pivot函数)0 2015-07-03 12:00:14在日常使用中,经常遇到这样的情况,需要将数据库中行转化成显示,如转化为这个时候,我们就需要使用pivot函数百度后,参考网址...完成了以下操作with temp...
  • Oracle数据库行转列与列转行方法

    千次阅读 2019-05-06 23:30:46
    –纵表横表 TABLE_A –> TABLE_B --方法一:使用case ... when ... then ... else ... end 函数 select name , sum ( case subject when '语文' then score else 0 end ) as chinese , ...
  • 想把mysql一个表的行转,图1是原表,想实现图2的样式SELECT MAX(CAS就是一个动态的行列转换 CREATE TABLE `c_wssb_zz` ( `aa011` varchar(10) default NULL, `aa001` varchar(20) default NULL, `aa014` varchar...
  • LISTAGG(SUBJECT||’:’||GRADE,’,’ ) WITHIN GROUP(ORDER BY NAME) AS SUBJECT FROM TBL_A GROUP BY NAME 得到的结果如图: 列转行 建表 CREATE TABLE TBL_B( NAME VARCHAR2(50), CHINESE NUMBER(12,2), ...
  • gp 行转列测试数据

    2019-03-27 15:25:47
    gp 行转列测试数据, 用于测试gp自带函数性能和自己实现的函数性能差别
  • 在许多的互联网项目当中,报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIV...
  • oracle的列转行函数

    2018-04-18 09:34:59
    SELECT TRIM(',' FROM SYS.STRAGG(A_NAME||NVL2(A_NAME,',','')))as nams FROM A_TEMP
  • 行转列及列转行查询

    千次阅读 2021-02-05 19:12:19
    开发过程中常遇到行转列或是列转行的问题,即需要将数据库中一张表信息进行行转列操作,再将每列(即每个字段)作为与其他表进行联表查询的字段进行显示。一、行转列:将原来同一列下多行的不同内容作为多个字段,输出...
  • 1、列转行 select year, – 当 a.month 的值 为 1 时 取值 a.index 否则 为 0 并取别名 为 index* max(DECODE(a.month,'1',a.index,'0')) as index1, max(DECODE(a.month,'2',a.index,'0')) as index2, max(DECODE(a...
  • 首先需要知道一个函数:concat_ws select ord,num,concat_ws('=',ord,num) from cx.over_test; 如果我们需要这样查询一条数据: 当给出一个数字,我们首先需要判断其是ord还是num,然后再取其对应的值的话,显示...
  • 如下所示: SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,  DATE_FORMAT(submit_date, '%Y-%m') zsubmit_date  FROM  表名 a  WHERE  a.statu = 3  AND a.rstatu = 2  AND a.job_dept IN ('19', '...
  • mysql 说明 oracle 说明 行转列 SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(#{keys},',',help_topic_id+1),',',-1)ASnum FROMmysql.help_topic WHEREhelp_topic_id...
  • db2数据库中的行转列和列转行

    千次阅读 2020-04-21 20:30:12
    一、行转列 给出下面的数据: CREATE TABLE Sales (Year INT, Quarter INT, Results INT) YEAR QUARTER RESULTS ----------- ----------- ----------- 2004 1 20 2004 ...
  • access行转列示例

    2015-06-21 14:52:06
    access行转列,可以行转列和列转行,非常实用。
  • Oracle行转列,列转行

    2022-01-07 09:26:48
    1.行转列 create table KECHENG( ID VARCHAR2(200), NAME VARCHAR2(200), COURSE VARCHAR2(200), SCORE VARCHAR2(200) ); INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('1','张三','语文','90'); ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,789
精华内容 3,915
关键字:

数据库行转列列转行