精华内容
下载资源
问答
  • Oracle数据库 列转行 函数wm_concat 和函数listagg
    2021-03-18 15:31:08

    列数据
    pid userid
    1 0000
    1 0001
    1 0002
    想要得到的结果
    pid userid
    1 0000,0001,0002

    方法一:
    函数wm_concat()
    select t.pid , wm_concat(t.userid) from oraceltable
    group by t.id
    存在问题 :版本为12G以上的该方法会出现乱码问题
    替代函数为 listagg

    方法二:
    函数listagg(userid,’,’)within group (order by )
    select id ,listagg(userid,’,’) within group(order by username) zhuanuserid from oraceltable group by id、

    listagg(userid,’,’); userid要转置的列名 ;
    within group(order by username); username 排序的列名;
    zhuanuserid ; 是转置后的列名;

    可能存在问题: 出现乱码问题;
    解决方案: to_char 一下
    例如: listagg(to_char(userid),’,’)

    更多相关内容
  • [一]、行转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,发现表中的数据就是列转成行了。

    展开全文
  • oracle列转行函数

    2018-04-18 09:34:59
    SELECT TRIM(',' FROM SYS.STRAGG(A_NAME||NVL2(A_NAME,',','')))as nams FROM A_TEMP
  • Oracle数据库列转行的两种方法

    千次阅读 2020-12-23 06:24:41
    列转行第一种方法,代码见下: select name, sum(decode(course, '数学', score)) 数学, sum(decode(course, '英语', score)) 英语, sum(decode(course, '语文', score)) 语文 from tb_student group by name;...

    1.创建tb_student表

    create table tb_student(

    id number(4) ,

    name varchar2(20),

    course varchar2(20),

    score number(5,2)

    );

    insert into tb_student values(1,'张三','语文',81);

    insert into tb_student values(2,'张三','数学',75);

    insert into tb_student values(3,'李四','语文',86);

    insert into tb_student values(4,'李四','数学',90);

    insert into tb_student values(5,'王五','语文',81);

    insert into tb_student values(6,'王五','数学',100);

    insert into tb_student values(7,'王五','英语',90);

    commit;

    表如下图:

    2.列转行第一种方法,代码见下:

    select

    name,

    sum(decode(course, '数学', score)) 数学,

    sum(decode(course, '英语', score)) 英语,

    sum(decode(course, '语文', score)) 语文

    from

    tb_student

    group by name;

    3.列转行第二种方法,代码见下:

    select

    name,

    sum(case course when '数学' then score else null end) 数学,

    sum(case course when '英语' then score else null end) 英语,

    sum(case course when '语文' then score else null end) 语文

    from

    tb_student

    group by name;

    最终结果如下图所示:

    decode语法:

    decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

    展开全文
  • oracle列转行

    2014-12-08 16:03:43
    oracle列转行的方法,有些时候页面显示要通过后台的列转行去实现,我们可以直接从sql中解决后台很复杂的问题
  • unpivot()函数需要Oracle版本大于等于11g--创建表createtableFruit(idint,namevarchar(20),Q1int,Q2int,Q3int,Q4int);--插入数据insertintoFruitvalues(1,'苹果',1000,2000,3300,5000);insertintoFruit...

    unpivot()函数

    需要Oracle版本大于等于11g

    --创建表

    create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);

    --插入数据

    insert into Fruit values(1,'苹果',1000,2000,3300,5000); 

    insert into Fruit values(2,'橘子',3000,3000,3200,1500); 

    insert into Fruit values(3,'香蕉',2500,3500,2200,2500); 

    insert into Fruit values(4,'葡萄',1500,2500,1200,3500);

    --查询数据

    select * from Fruit;

    --列转行查询

    select id, name, jidu, xiaoshou from Fruit unpivot(xiaoshou for jidu in(q1, q2, q3, q4))

    注意:

    unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量。等同于下面的SQL:

    select id, name, 'Q1' jidu, (select q1 from fruit where id = f.id) xiaoshou from Fruit f 

    union 

    select id, name, 'Q2' jidu, (select q2 from fruit where id = f.id) xiaoshou from Fruit f 

    union 

    select id, name, 'Q3' jidu, (select q3 from fruit where id = f.id) xiaoshou from Fruit f 

    union 

    select id, name, 'Q4' jidu, (select q4 from fruit where id = f.id) xiaoshou from Fruit f


    展开全文
  • 目前该方法只适合在oracle数据库中使用。该方法只需要sql语句就可以实现列转行。  下面给出该方法的示例: select a,b,c from(with test as (select ‘aaa’ a,’bbb’ b,’1,2,3′ c from dual)select a,b,substr...
  • * 各位大佬我现在就是这样 select出来有数据的 !... * 然后我PIVOT 的话就会报错 !...*他这边PIVOT 的in里面的类型要和我查出来的dl_name数量对应嘛 他好多数量都不是固定的 !...* 我语法有问题吧 他这个的正确写法应该是...
  • Oracle 列转行函数pivot

    2021-05-02 04:52:57
    [url=]行转[/url]、列转行是我们经常会遇到的“诡异”需求。标准SQL没有提供此类型操作的支持函数,早期Oracle的版本中,我们也只能通过复杂的变通手段实现。在Oracle11g中,这种情况改变了,我们可以利用[url=]...
  • oracle列转行方法总结

    2021-05-03 08:20:47
    方法一:-------------------------------------------------------------------Muti-row to line(col2row)----------------------------------------------------------------create or replace type str_tab is ...
  • Oracle列转行的函数

    2021-07-16 11:58:43
    listagg是一个列转行的函数,返回一个varchar2的字符串,measure_expr为要转换的,可以多,delimiter是measure_expr之间拼接的分隔符, 可以没有,within group 中的order_by_caluse是measure_expr中值的排序。...
  • 一、行转listagg函数: 场景:这里的表数据使用的是oracle默认的scott账户下的emp(员工)表。 规范写法 : LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段) 通常情况下,LISTAGG是满足需要的,LISTAGG ...
  • 多行转字符串 这个比较简单,用||或concat函数可以实现 ... 字符串转多 实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式 字符串转多行 使用union all函数等方式...
  • oracle中行转列转行

    千次阅读 2021-02-23 14:43:55
    一、行转 1) 使用decode函数 select name, sum(decode(course, ‘语文’, score, 0)) as 语文, sum(decode(course, ‘数学’, score, 0)) as 数学, sum(decode(course, ‘英语’, score, 0)) as 英语 from GRADE...
  • [sql] 代码如下: create table wlbtest1( username varchar2(2), deptID number ); create table wlbtest2( deptName varchar2(10), deptID number ); insert into wlbtest1 values (‘a’, 1);...
  • 行转函数--pivot欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居...
  • 把字符串 '1,2,3,4,5',以逗号分隔,输出为行,也就是12345随手写了一个, Oracle 10G 以上有表如下SQL>select*fromt;IDNAME--------------------------10,1,5,2,8,1029,7,83你好,他好,大家好withvmaxnumas...
  • Oracle数据库之行转列转行方法

    千次阅读 2019-05-06 23:30:46
    –创建纵表TABLE_A CREATE TABLE TABLE_A ( NAME VARCHAR2(20), SUBJECT VARCHAR2(20), SCORE NUMBER(22,0)); ...–向表TABLE_A中插入数据 ...INSERT INTO TABLE_A (NAME, SUBJECT, SCORE) VALUES (‘张三’, ‘语文’...
  • 文章目录列转行Unpivot函数用法 列转行Unpivot函数用法 如每年的各项指标数据,数据格式如下: SELECT T.FC_Y AS FORECAST_YEAR, --年份 T.EPS AS IDX_EPS, -- 指标1 T.OPER_INC AS IDX_OPER_INC,-- 指标2 T...
  • 转字符串拼接 select id||username str from dual 字符串拆分方式: substr、instr、regexp_substr函数方式 行转 wm_concat函数 select wm_concat(name) name from dual; SELECT to_char(wm_concat...
  • 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), ...
  • Oracle行转列转行的Sql语句总结多行转字符串这个比较简单,用||或concat函数可以实现SQL Code12selectconcat(id,username)strfromapp_userselectid||usernamestrfromapp_user字符串转多实际上就是拆分字符串的...
  • 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'); ...
  • [数据库]Oracle行转(使用pivot函数)0 2015-07-03 12:00:14在日常使用中,经常遇到这样的情况,需要将数据库中行转化成显示,如转化为这个时候,我们就需要使用pivot函数百度后,参考网址...完成了以下操作with temp...
  • Oracle 行转列转行 的Sql语句总结
  • 本文主要向大家介绍了Oracle数据库之Oracle根据【日期】组,其他条件根据PIVOT行转,通过具体的内容向大家展现,希望对大家学习Oracle数据库有所帮助。selectOPER_TIME,MICROPAY,REFUNDfrom(selecttrunc(oper_time...
  • Oracle列转行函数 Listagg() 语法详解及应用实例

    万次阅读 多人点赞 2017-01-03 22:16:54
    工作中用到一段比较复杂的...说简单点,listagg()函数可以实现多记录聚合为一条记录,从而实现数据的压缩、致密化(data densification)。以下内容转载自http://dacoolbaby.iteye.com/blog/1698957,SQL脚本做了...
  • oracle列转行 listagg

    2021-06-07 21:24:39
    oracle 列转行listagg实现方式 当结果中只有要转化的那一时候 select listagg(t.shopName, ',') within group(order by t.idCno) from( select idCno, shopName from shop where idCno in (select p.idCno ...
  • Oracle行转wm_concat()

    2021-10-08 16:03:25
    原数据 select id,name from T_SJZX_DMJZX where ssdm='GBT_XZQH' and rownum <...行转 select wm_concat(name) from T_SJZX_DMJZX where ssdm='GBT_XZQH' and rownum <6; 执行结果如下: W

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,443
精华内容 1,777
关键字:

oracle数据库列转行