精华内容
下载资源
问答
  • sql语句列转行
    千次阅读
    2021-03-05 16:10:44

    SQL 列转行函数

    Oracle:

    listagg()

    **listagg(字段名,’‘分隔符’) within group(order by 字段名) **

    with temp as(  
      select 'China' nation ,'Guangzhou' city from dual union all  
      select 'China' nation ,'Shanghai' city from dual union all  
      select 'China' nation ,'Beijing' city from dual union all  
      select 'USA' nation ,'New York' city from dual union all  
      select 'USA' nation ,'Bostom' city from dual union all  
      select 'Japan' nation ,'Tokyo' city from dual   
    )  
    select nation,listagg(city,',') within GROUP (order by city)  cities
    from temp  
    group by nation
    
    
    
    

    执行结果

    NATIONCITIES
    ChinaBeijing,Guangzhou,Shanghai
    JapanTokyo
    USABostom,New York

    wm_concat()

    使用wm_concat能达到同样的效果:

    select nation,wm_concat(city) from temp group by nation
    

    MySQL:

    下表为 节点表:

    id parent_id name

    1 0 根节点
    2 1 一级节点
    3 1 一级节点
    4 1 一级节点
    5 3 二级节点
    6 2 二级节点
    7 4 二级节点
    8 5 三级节点
    9 5 三级节点
    10 5 三级节点
    11 6 三级节点
    12 11 四级节点
    13 10 四级节点

    使用group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

    select t.name, GROUP_CONCAT(t.id ORDER BY t.`name` SEPARATOR ',') ids from tree t GROUP BY t.name
    
    

    查询结果:

    nameids
    一级节点4,3,2
    三级节点11,10,9,8
    二级节点7,6,5
    四级节点13,12
    根节点1





    更多相关内容
  • oracle sql 列转行

    2022-05-09 18:17:11
    数据sql: select '123' meter_id,'2022-05-05' data_date,'1' phase_...列转行sql: SELECT meter_id, data_date, phase_flag, cur_val1 cur_val FROM ( SELECT '123' meter_id, '2022-05-05' data_date, '1'

    数据准备sql代码

    select '123' meter_id,'2022-05-05' data_date,'1' phase_flag,'55' cur_val1,'66' cur_val2,'77' cur_val3 from dual
    

    数据格式

    在这里插入图片描述

    列转行sql代码

    SELECT
    	meter_id,
    	data_date,
    	phase_flag,
    	cur_val1 cur_val 
    FROM
    	( SELECT '123' meter_id, '2022-05-05' data_date, '1' phase_flag, '55' cur_val1, '66' cur_val2, '77' cur_val3 FROM dual ) UNION ALL
    SELECT
    	meter_id,
    	data_date,
    	phase_flag,
    	cur_val2 cur_val 
    FROM
    	( SELECT '123' meter_id, '2022-05-05' data_date, '1' phase_flag, '55' cur_val1, '66' cur_val2, '77' cur_val3 FROM dual ) UNION ALL
    SELECT
    	meter_id,
    	data_date,
    	phase_flag,
    	cur_val3 cur_val 
    FROM
    	( SELECT '123' meter_id, '2022-05-05' data_date, '1' phase_flag, '55' cur_val1, '66' cur_val2, '77' cur_val3 FROM dual )
    

    列转行后数据格式

    在这里插入图片描述

    展开全文
  • Mysql 行转列转行 SQL语句和示例表结构SQL Mysql 行转列转行 SQL语句和示例表结构SQL
  • 多行转字符串 这个比较简单,用||或concat函数可以实现 ? 1 ... select concat(id,username) str from ... 字符串转多 实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式 ...

    多行转字符串

    这个比较简单,用||或concat函数可以实现

    ?

    1

    select concat(id,username) str from app_userselect id||username str from app_user

    字符串转多列

    实际上就是拆分字符串的问题,可以使用 substr、instr、regexp_substr函数方式

    字符串转多行

     

    使用union all函数等方式

    wm_concat函数

    首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

     

     

    ?

    1

    create table test(id number,name varchar2(20));insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');

     

    效果1 : 行转列 ,默认逗号隔开

     

     

    ?

    1

    select wm_concat(name) name from test;

    /

     

    效果2: 把结果里的逗号替换成"|"

     

    ?

    1

    select replace(wm_concat(name),',','|') from test;

    /

     

    效果3: 按ID分组合并name

    ?

    1

    select id,wm_concat(name) name from test group by id;

    /
    sql语句等同于下面的sql语句

    ?

    1

    -------- 适用范围:8i,9i,10g及以后版本  ( MAX + DECODE )select id, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ',' || name, null)) || max(decode(rn, 3, ',' || name, null)) str       from (select id,name,row_number() over(partition by id order by name) as rn from test) t group by id order by 1;      -------- 适用范围:8i,9i,10g及以后版本 ( ROW_NUMBER + LEAD )select id, str from (select id,row_number() over(partition by id order by name) as rn,name || lead(',' || name, 1) over(partition by id order by name) ||    lead(',' || name, 2) over(partition by id order by name) || lead(',' || name, 3) over(partition by id order by name) as str from test) where rn = 1 order by 1;  -------- 适用范围:10g及以后版本 ( MODEL )select id, substr(str, 2) str from test model return updated rows partition by(id) dimension by(row_number() over(partition by id order by name) as rn) measures (cast(name as varchar2(20)) as str) rules upsert iterate(3) until(presentv(str[iteration_number+2],1,0)=0) (str[0] = str[0] || ',' || str[iteration_number+1]) order by 1;              -------- 适用范围:8i,9i,10g及以后版本 ( MAX + DECODE )select t.id id,max(substr(sys_connect_by_path(t.name,','),2)) str from (select id, name, row_number() over(partition by id order by name) rn from test) t       start with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t.id;</span>

    懒人扩展用法:

    案例: 我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单,假设我的APP_USER表中有(id,username,password,age)4个字段。查询结果如下

     

     

    ?

    1

    /** 这里的表名默认区分大小写 */ select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER' sqlStr from user_tab_columns where table_name='APP_USER';

    /

     

    利用系统表方式查询

     

    ?

    1

    select * from user_tab_columns

    /
     

     

     

    Oracle 11g 行列互换 pivot 和 unpivot 说明

     

    在Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列) 和unpivot(列转行)

    参考:http://blog.csdn.net/tianlesoftware/article/details/7060306、http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

     

    google 一下,网上有一篇比较详细的文档:http://www.oracle-developer.net/display.php?id=506

    pivot 列转行

    测试数据 (id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。

     

    ?

    1

    create table demo(id int,name varchar(20),nums int);  ---- 创建表insert into demo values(1, '苹果', 1000);insert into demo values(2, '苹果', 2000);insert into demo values(3, '苹果', 4000);insert into demo values(4, '橘子', 5000);insert into demo values(5, '橘子', 3000);insert into demo values(6, '葡萄', 3500);insert into demo values(7, '芒果', 4200);insert into demo values(8, '芒果', 5500);

    /

     

    分组查询 (当然这是不符合查询一条数据的要求的)

    ?

    1

    select name, sum(nums) nums from demo group by name

     

    /

    行转列查询

     

    ?

    1

    select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果' 苹果, '橘子', '葡萄', '芒果'));


    /

     

    注意: pivot(聚合函数 for 列名 in(类型)) ,其中 in(‘’) 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers

    当然也可以不使用pivot函数,等同于下列语句,只是代码比较长,容易理解

     

    ?

    1

    select * from select sum(nums) 苹果 from demo where name='苹果'),(select sum(nums) 橘子 from demo where name='橘子'),       (select sum(nums) 葡萄 from demo where name='葡萄'),(select sum(nums) 芒果 from demo where name='芒果');

    unpivot 行转列

    顾名思义就是将多列转换成1列中去
    案例:现在有一个水果表,记录了4个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。

     

    创建表和数据

     

    ?

    1

    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

    /

     

    列转行查询

     

    ?

    1

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

    注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量
    /

     

    同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高

     

    ?

    1

    select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit funionselect id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f

     

    XML类型

    上述pivot列转行示例中,你已经知道了需要查询的类型有哪些,用in()的方式包含,假设如果您不知道都有哪些值,您怎么构建查询呢?

    pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出,在此输出中,您可以指定一个特殊的子句 ANY 而非文字值

    示例如下:

     

    ?

    1

    select * from (   select name, nums as "Purchase Frequency"   from demo t)                              pivot xml (   sum(nums) for name in (any))

    /
    如您所见,列 NAME_XML 是 XMLTYPE,其中根元素是 <PivotSet>。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。

     

     

     

    /

     

    结论

     

    Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表,而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样,您可以使用 unpivot 操作转换任何交叉表报表,以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出,您不必指定 pivot 操作需要搜索的值域。

     

    转自:https://www.cnblogs.com/yewg/p/5510594.html

    展开全文
  • SQL Code select A.* into #result from ( select '唐三' as name,'缠绕' as skill union all select '唐三' as name,'寄生' as skill union all select '唐三' as name,'蛛网束缚' as sk

    SqlServer多行转字符串

    原数据

    结果数据库

    通过FOR XML PATH把skill按-拼接在一起(-缠绕-寄生-蛛网束缚),然后使用STUFF函数把第一个-符号去掉。

    SQL Code

    select A.* 
    into #result
    from
    (
    	select '唐三' as name,'缠绕' as skill
    	union all
    	select '唐三' as name,'寄生' as skill
    	union all
    	select '唐三' as name,'蛛网束缚' as skill
    	union all
    	select '小舞' as name,'爆杀八段摔' as skill
    	union all
    	select '小舞' as name,'无敌金身' as skill
    ) A
    
    SELECT name,
    STUFF(
    (SELECT '-'+ A1.skill FROM #result A1 WHERE A1.name=A.name FOR XML PATH(''))
    ,1,1,''
    ) AS skill
    FROM #result A
    GROUP BY name
    order by name
    
    drop table #result

    SqlServer字符串多行转

    原数据

    结果数据库

    通过CONVERT(xml,'<root><v>' + REPLACE(A.skill, '-', '</v><v>') + '</v></root>')把skill转换成xml文档,再通过OUTER APPLY把xml转成多行,并通过A.skill.nodes('/root/v') N(v)取到xml中拆分好的节点数据。

    SQL Code

    select A.* 
    into #result
    from
    (
    	select '唐三' as name,'缠绕-寄生-蛛网束缚' as skill
    	union all
    	select '小舞' as name,'爆杀八段摔-无敌金身' as skill
    ) A
    
    select A.name,B.skill from
    (
    select A.name,skill=CONVERT(xml,'<root><v>' + REPLACE(A.skill, '-', '</v><v>') + '</v></root>')
    from #result A
    )A
    OUTER APPLY(
    SELECT skill = N.v.value('.', 'varchar(100)') FROM A.skill.nodes('/root/v') N(v)
    )B
    
    drop table #result
    

    Oracle字符串多行转

    原数据

    结果数据库

    SQL Code

    select distinct * from (
    select  name,regexp_substr(A.skill, '[^-]+', 1, Level,'i') skill
      from (
      select '唐三' as name,'缠绕-寄生-蛛网束缚' as skill from dual
      union all
      select '小舞' as name,'爆杀八段摔-无敌金身' as skill from dual
      ) A
    connect by Level <= LENGTH(A.skill) - LENGTH(REGEXP_REPLACE(A.skill, '-', '')) + 1) order by name;

    SqlServer查询当前库所有存储是否包含某个表

    SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%表名%'
    AND ROUTINE_TYPE='PROCEDURE'

     

    展开全文
  • --注意:pivot后必须跟max或min等函数,不能直接写字段名 列转行 SELECT FSX,TYPE,VAL FROM ( SELECT TYPE, VAL FROM ( SELECT FXY1,FXY2 from TABLE_A EVA LEFT JOIN TABLE_B ENT ON EVA.SP_ID = ENT.id LEFT JOIN...
  • 主要介绍了SQL行转列转行代码详解,涉及动态方案和静态方案两种处理方式,具有一定参考价值,需要的朋友可以了解下。
  • SQL Server列转行

    千次阅读 2021-12-17 14:30:26
    SQL Server列转行SQL实现列转行UNPIVOT SQL实现列转行 今天在做一个需求的时候遇到了这个列转行,原因是需要存几个固定的数据,类似于学生成绩,学科是固定的,分数是不固定的,为了不影响之前已经做好的功能,...
  • SQL行转列转行

    万次阅读 多人点赞 2018-09-09 15:51:14
    SQL行转列转行 这个主题还是比较常见的,行转主要适用于对数据作聚合统计,如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。 一、整理测试数据 create table wyc_test( id int(32)...
  • 主要给大家介绍了关于SQL行转列转行的简单实现方法,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • sql查询:行转列转行

    千次阅读 2020-10-31 21:32:34
    表及数据sql: CREATE TABLE `tb_score` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(20) NOT NULL COMMENT '用户id', `subjectName` varchar(20) DEFAULT NULL COMMENT '科目', `score` ...
  • sql的行转(PIVOT)与列转行(UNPIVOT)

    千次阅读 2018-07-26 19:43:38
    在做数据统计的时候,行转列转行是经常碰到的问题。case when方式太麻烦了,而且可扩展性不强,可以使用 PIVOT,UNPIVOT比较快速实现行转列转行,而且可扩展性强 一、行转 1、测试数据准备 CREATE ...
  • SQL SERVER 列转行 的两种方法

    千次阅读 2021-02-01 10:19:08
    行转 两种方法 2.1使用 UNPIVOT 进行 列转行 完整语法 table_sourceUNPIVOT(value_columnFOR pivot_columnIN()) SELECT a.Name,a.CLASS_NAME,a.SCORE FROMScoreUNPIVOT (CLASS_NAME FOR SCORE IN(数学,语文,英文)...
  • SQLServer列转行函数Unpivot

    千次阅读 2020-09-24 10:47:38
    SQLServer列转行函数Unpivot会将多转化为多行,列名转换为值,会新增两个column:一个column用于存储列名,一个column用于存储值。
  • 目录结构如下:行转列列转行[一]、行转1.1、初始测试数据表结构:TEST_TB_GRADESql代码create table TEST_TB_GRADE(ID NUMBER(10) not null,USER_NAME VARCHAR2(20 CHAR),COURSE VARCHAR2(20 CHAR),SCORE FLOAT)...
  • --行转 固定xml语法 declare @xml xml ; set @xml=cast('<v>2</v><v>4</v><v>3</v><v></v>' as xml); select tab.val.value('.'/*xpath 语法*/,'nvarchar(10....
  • SQL关键字:行转【union】;列转行【case ...when...then】
  • sql查询结果列转行

    2021-10-19 10:47:38
    SQL语句如下: SELECT SNAME, SUM(CASE WHEN CNAME = 'JAVA' THEN SCORE ELSE 0 END) JAVA, SUM(CASE WHEN CNAME = 'MYSQL' THEN SCORE ELSE 0 END) mysql FROM sql_select GROUP BY SNAME ...
  • 2.列转行后的数据 SELECT tt.订单号, tt.物料代码, tt.物料名称, tt.物料规格, tt.订单数量, tt.生产计划, tt.TODAY, tt.开始时间 INTO #OrderPlanPrint1 FROM #OrderPlanPrint UNPIVOT ( T...
  • Oracle行转列转行Sql语句总结多行转字符串这个比较简单,用||或concat函数可以实现SQL Code12selectconcat(id,username)strfromapp_userselectid||usernamestrfromapp_user字符串转多实际上就是拆分字符串的...
  • 行转应用
  • Sql语句实现表的行列转换,行转列转行
  • 题: ...列转行 CASE expr1 WHEN expr2 THEN expr3 ELSE expr4 END 行中字段expr1是expr2取expr3否则取expr4 根据题意 CASE month WHEN 'jan' THEN revenue ELSE null END 当字段month是‘jan’
  • sql行转列转行

    2021-07-27 10:53:25
    考察的问题就是通过SQL语句实现在这两种形态间转换,其中长表转为宽表即行转,宽表转为长表即列转行。 01 行转:sum+if 在行转中,经典的解决方案是条件聚合,即sum+if组合。其基本的思路是这样的: 在长表的...
  • SqlServer行转&列转行

    千次阅读 2022-01-06 18:32:13
    2、SQLServer 2.1、行转 -- 第一种方法 select name, max(case cource when '语文' then score else 0 end) as chinese, max(case cource when '数学' then score else 0 end) as math, max(case cource when '...
  • SQL语句列转行.

    2013-05-20 10:53:48
    列转行成功. 但是带入机构条件就提示不是单组分组函数. 去掉 b.branchcode||'-'||b.branchname as bran, 这一段,就可以显示了 (需求里必须显示机构咧.) 麻烦了 --按脱保原因调查表的统计方法 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,463
精华内容 2,585
关键字:

sql语句列转行