精华内容
下载资源
问答
  • sql行专列列转行
    2022-06-09 17:33:20

    hive sql 行转列、列转行【全网最清楚】

    根据一些字段例子可以更好理解

    字段字段释义
    id数据id
    type_类型
    value_类型对应的值
    type_ 1类型1
    type_2类型2
    value_1类型1对应的值
    value_ 2类型2对应的值
    select id,type_,value_ from table_name
    	lateral view explode(
    		map(type_1,value_1
    			type_2,value_2)
    	)  a as type_,value_
    

    以上结果为列转行,将五列结果转换成三列,根据自己的需求,可以作调整;
    行转列可以用case when实现。

    更多相关内容
  • NULL 博文链接:https://xkxjy.iteye.com/blog/602387
  • SQL行专列列转行的存储过程 很实用的 SQL行专列列转行的存储过程 很实用的
  • ( T-SQL_经典行专列列转行_分页及存储过程 T-SQL_经典行专列列转行_分页及存储过程
  • SQL行列转行

    2022-07-07 14:06:32
    SQL

    案例1、每个产品在不同商店的价格

    🚀 表:Products
    +-------------+---------+
    | Column Name | Type    |
    +-------------+---------+
    | product_id  | int     |
    | store1      | int     |
    | store2      | int     |
    | store3      | int     |
    +-------------+---------+
    这张表的主键是product_id(产品Id)。
    每行存储了这一产品在不同商店store1, store2, store3的价格。
    如果这一产品在商店里没有出售,则值将为null。


    🚀 需求
    请你重构 Products 表,查询每个产品在不同商店的价格,使得输出的格式变为(product_id, store, price) 。
    如果这一产品在商店里没有出售,则不输出这一行。
    输出结果表中的 顺序不作要求 。
    查询输出格式请参考下面示例。

    示例 1:

    输入:
    Products table:
    +------------+--------+--------+--------+
    | product_id | store1 | store2 | store3 |
    +------------+--------+--------+--------+
    | 0          | 95     | 100    | 105    |
    | 1          | 70     | null    | 80      |
    +------------+--------+--------+--------+
    输出:
    +------------+--------+-------+
    | product_id | store  | price |
    +------------+--------+-------+
    | 0          | store1 | 95    |
    | 0          | store2 | 100   |
    | 0          | store3 | 105   |
    | 1          | store1 | 70    |
    | 1          | store3 | 80    |
    +------------+--------+-------+
    解释:
    产品0在store1,store2,store3的价格分别为95,100,105。
    产品1在store1,store3的价格分别为70,80。在store2无法买到。

    🐴🐴 答案

    方法1.
    select product_id, 'store1' store, store1 price
    from products
    where store1 is not null 
    union
    select product_id, 'store2' store, store2 price
    from products 
    where store2 is not null
    union
    select product_id, 'store3' store, store3 price
    from products 
    where store3 is not null

    方法2.
    select 
            product_id "product_id",
            lower(store) as "store",
            price "price"
    from Products
    unpivot(price for store in(store1,store2,store3))

    案例2、行列转换的简单实现

    行转列

    假如我们有下表:

    🚀 表:Student

    +------------+--------+-------+
    | name  | subject  | score |
    +------------+--------+-------+
    | 张三     | 语文    | 96    |
    | 张三     | 数学    | 98    |
    | 张三     | 英语    | 95    |
    | 李四     | 语文    | 92    |
    | 李四     | 数学    | 96    |
    | 李四     | 英语    | 98    |
    +------------+--------+-------+

    select *
    from Student 
    pivot(
            sum(score) for subject in(语文,数学,英语)
    )

    通过上面 SQL 语句即可得到下面的结果

    +------------+--------+--------+--------+
    | 姓名     | 语文   | 数学   | 英语   |
    +------------+--------+--------+--------+
    | 张三     | 96     | 98     | 95    |
    | 李四     | 92     | 96     | 98    |
    +------------+--------+--------+--------+

    PIVOT 后跟一个聚合函数来拿到结果,FOR 后面跟的科目是我们要转换的列,这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。

    当然我们也可以用 CASE WHEN 得到同样的结果,就是写起来麻烦一点。

    select name,
            max(
            case 
                    when subject='语文'
                    then score
                    else 0
            end) as "语文",
            max(
            case 
                    when subject='数学'
                    then score
                    else 0
            end) as "数学",
            max(
            case 
                    when subject='英语'
                    then score
                    else 0
            end) as "英语"
    from Student
    group by name

    使用 CASE WHEN 可以得到和 PIVOT 同样的结果,没有 PIVOT 简单直观。

    列转行

    假设我们有下表 student1

    🚀 表:Student1

    +------------+--------+--------+--------+
    | 姓名     | 语文   | 数学   | 英语   |
    +------------+--------+--------+--------+
    | 张三     | 96     | 98     | 95    |
    | 李四     | 92     | 96     | 98    |
    +------------+--------+--------+--------+

    select * 
    from Student1
    unpivot(
            score for subject in ("语文","数学","英语")
    )

    通过 UNPIVOT 即可得到如下结果:

    +------------+--------+-------+
    | name  | subject  | score |
    +------------+--------+-------+
    | 张三     | 语文    | 96    |
    | 张三     | 数学    | 98    |
    | 张三     | 英语    | 95    |
    | 李四     | 语文    | 92    |
    | 李四     | 数学    | 96    |
    | 李四     | 英语    | 98    |
    +------------+--------+-------+

    我们也可以使用下面方法得到同样结果

    select
            name,
            '语文' as subject,
            max("语文") as score
    from Student1 group by name
    union
    select
            name,
            '数学' as subject,
            max("数学") as score
    from Student1 group by name
    union
    select
            name,
            '英语' as subject,
            max("英语") as score
    from Student1 group by name

    同样,使用 CASE WHEN 可以得到和 PIVOT 同样的结果,但没有 PIVOT 简单直观。

    展开全文
  • SQL 行专列列转行

    2019-10-06 13:45:47
    SQl 行专列列转行 有几种转换方法: 一:SQL行: 创建表,插入数据 create table tb(name varchar(10),pro varchar(10),sorce int)insert into tb values('张三','语文',74)insert into tb values('张三...

        SQl 行专列,列转行 有几种转换方法:

     

    一:SQL行转列:

      创建表,插入数据


    create table tb(name varchar(10),pro varchar(10),sorce int)
     
    insert into tb values('张三','语文',74)
     
    insert into tb values('张三','数学',83)
     
    insert into tb values('张三','物理',93)
     
    insert into tb values('李四','语文',74)
     
    insert into tb values('李四','数学',84)
     
    insert into tb values('李四','物理',94)

     

       1.在转换列个数固定并且已知要转换的列的内容的方式下,可以直接通过拼接SQl的方式 如:
      
    select name,
    MAX( case pro when '语文' then Sorce else 0 end ) 语文,
    MAX( case pro when '数学' then Sorce else 0 end ) 数学,
    MAX( case pro when '物理' then Sorce else 0 end ) 物理
    from tb 
    group by name

    2. 利用 pivot 函数:

    declare @sql varchar(8000)   set @sql=''  --初始化变量@sql 

      select @sql=@sql+','+pro from tb group by pro--变量多值赋值

    set @sql=stuff(@sql,1,1,'')--去掉首个','

    print @sql

    --测试写死 select * from tb pivot(max(sorce) for  pro in(语文,数学,物理,hhh)) aa

    --动态获取 set @sql='select * from tb pivot (max(sorce) for pro in ('+@sql+'))a'   exec(@sql)

     

    二:列转行

    1.用unpivot 函数

    create table tb1(姓名 varchar(10),语文 int,数学 int,物理 int)  

    insert into tb1 values('张三',74,83,93)  

    insert into tb1 values('李四',74,84,94)  

    go  

    select 姓名,课程,分数 from tb1 unpivot (分数 for 课程 in([语文],[数学],[物理])) t

     

    其他:

    STUFF ( character_expression , start , length ,character_expression )

    删除指定长度的字符,并在指定的起点处插入另一组字符。

    以下示例在第一个字符串 abcdef 中删除从第 2 个位置(字符 b)开始的三个字符,然后在删除的起始位置插入第二个字符串,从而创建并返回一个字符串

    SELECT STUFF('abcdef', 2, 3, 'ijklmn') GO

    下面是结果集

    aijklmnef

     

     

     

     

    转载于:https://www.cnblogs.com/79rain/archive/2012/08/10/2631297.html

    展开全文
  • hive sql &列转行

    2021-04-18 11:28:14
    数据的存储有时候存在一个主键对应多行数据记录的情况,如果我们想把数据做(合并)操作,就可以使用concat_ws(’,’, collect_set(column_name))函数,返回string。仅仅使用collect_set(column_name)函数返回...

    数据的存储有时候存在一个主键对应多行数据记录的情况,如果我们想把数据做行转列(合并)操作,就可以使用collect_set()和concat_ws()函数嵌套,返回string。

    concat_ws(',', collect_set(column_name))
    

    仅仅使用collect_set(column_name)函数返回的是数组,见下边第3条说明。
    说明:collect_set()去重,collect_list()不去重,column_name的数据类型要求是string

    1、多行转列

    create table  students_info
    (`sno` string comment '学生编号',
    `name` string comment '姓名',
    `depart` string comment '选修课程'
    )
    --学生信息表数据插入
    insert into  students_info  values    (103,'张三','公司法')
                                            ,(103,'张三','心理学')
                                            ,(105,'王五','python程序设计')
                                            ,(109,'李麻子','数据结构与算法')
                                            ,(109,'李麻子','机器学习');
    
    select * from  students_info
    
    

    在这里插入图片描述
    对数据进行行转列操作

    select sno, name, concat_ws(',', collect_set(depart)) as depart from students_info
    group by sno, name
    

    在这里插入图片描述

    2、列转多行
    使用函数lateral view explode(split(column_name, ‘,’)) new_column_name

    create table  students_info
    (`SNO` string  comment '学生编号',
    `name` string  comment '姓名',
    `DEPART` string  comment '选修课程'
    )
    --成绩表数据插入
    insert into  students_info  values (103,'张三','公司法,心理学')
                                                    ,(105,'王五','python程序设计')
                                                    ,(109,'李麻子','数据结构与算法,机器学习');
    
    select * from  students_info
    

    在这里插入图片描述对数据进行列转行操作

    select sno, name, add_depart from students_info a
    lateral view explode(split(a.depart, ',')) b as add_depart
    

    在这里插入图片描述
    3、多行转列之collect_list()和collect_set()
    Hive中collect相关的函数有collect_list和collect_set,它们都是将分组中的某列转为一个数组返回,不同的是collect_list不去重而collect_set去重。

    创建一张实验用表,存放用户每天点播视频的记录:

    create table t_visit_video
    (username string,
     video_name string
    ) partitioned by (day string)
    
    --学生信息表数据插入
    insert into  t_visit_video  values    ('张三','大唐双龙传')
                                            ,('张三','神探狄仁杰')
                                            ,('王五','机器人总动员')
                                            ,('王五','放牛班的春天')
                                            ,('王五','盗梦空间')
                                            ,('李四','天下无贼')
                                            ,('李四','霸王别姬')
                                            ,('李四','霸王别姬');
    
    

    在这里插入图片描述
    按用户分组,取出每个用户每天看过的所有视频的名字:

    select username, collect_list(video_name) from t_visit_video group by username;
    

    在这里插入图片描述
    上面的查询结果存在视频名称重复情况,因为霸王别姬实在太好看了,所以李四这家伙看了两遍,这直接就导致得到的观看过视频列表有重复的,所以应该增加去重,使用collect_set,其与collect_list的区别就是会去重:

    select username, collect_set(video_name) from t_visit_video group by username;
    

    在这里插入图片描述
    李四的观看记录中霸王别姬只出现了一次,实现了去重效果。

    突破group by限制
    还可以利用collect来突破group by的限制,Hive中在group by查询的时候要求出现在select后面的分组列都必须是出现在group by后面的,即select分组列必须是作为分组依据的列,但是有的时候我们想根据字段A进行分组然后随便取出每个分组中的一个字段B,代入到这个实验中就是按照用户进行分组,然后随便拿出一个他看过的视频名称即可:

    select username, collect_list(video_name)[0] from t_visit_video group by username;
    

    在这里插入图片描述
    video_name不是分组列,依然能够取出这列中的数据。

    实际案例:
    collect_set()和regexp()结合使用

    --在正则表达式中"|"表示“或”
    select case when pro_code regexp '43|45|' then '白名单'
                when pro_code regexp '44|46|56' then '非白名单' 
    		    else '商户付息' end as is_whitelist,
      count(distinct a.partner_id) as store_number
    from databasex.temp_partner_info a
    left join (select partner_id, concat_ws(',',collect_set(market_pro_code)) as pro_code 
               from databasex.temp_partner_prod_map 
    		   where dt='${pdate}' group by partner_id) d 
    on a.partner_id=d.partner_id
    where a.dt='${pdate}'
    and a.partner_type='2'
    group by case when pro_code regexp '43|45|' then '白名单'
                  when pro_code regexp '44|46|56' then '非白名单' 
    		      else '商户付息' end
    

    内容参考链接:https://zhuanlan.zhihu.com/p/59351085,
    https://www.cnblogs.com/kimbo/p/6208973.html,
    https://www.cnblogs.com/cc11001100/p/9043946.html
    仅供学习用,如有侵权请联系删除。

    展开全文
  • 示例1: sql 2. 列转行案例 示例1 sql 题目来自leetcode 每个产品在不同商店的价格 计算特殊奖金
  • 列转行是我们经常碰到的问题。一般通过CASE WHEN 语句来实现,也可以通过 SQL SERVER 的运算符PIVOT来实现。用传统的方法,比较好理解。层次清晰,而且比较习...
  • mysql列转行  语句不难,不做多余解释了,看语句时,从内往外一句一句剖析  有如图所示的表,现在希望查询的结果将转成  建表语句如下: CREATE TABLE `TEST_TB_GRADE` ( `ID` int(10) NOT...
  • SQLSERVER 列转行

    2021-03-01 16:24:53
    1. 将项目表的ApplySys 拆分为多条数据 SELECT ApplySys , * FROM p_project SELECT * FROM dbo.myApplication – SELECT ProjGuid,ApplySys,c.ApplicationName FROM ( SELECT A.ProjGuid, B.ApplySys ...
  • sql如何列转行

    千次阅读 2021-11-17 15:23:42
    我们都知道在sql查询中,union 是增,join是增,但是如果碰到需要列转行时,一味的使用 join来完成则会变的异常繁杂。如把一天24小时的转换成,则需要join24次,既不方便管理也使sql运行效率很慢,...
  • 1.列转行 创建表格: create table if not exists student( name varchar(20), subject varchar(10), score int(10)); 插入数据后: insert into student values ('张三','语文',76), ('张三','数学',86), ('...
  • SqlServer&列转行

    千次阅读 2022-01-06 18:32:13
    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 '英语' then ...
  • 可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式中的一个的唯一值转换为输出中的多,来轮替表值...PIVOT () 原表 pivot 后 列转行(unpivot) 原表 unpivot 后.
  • 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中行转列转行

    千次阅读 2022-03-22 14:10:59
    主要用到两个函数pivot 和 unpivot pivot通过将表达式某一中的唯一值准换为输出中的多个来旋转表值表达式,并在必要时对最终输出中所需的任何其余值执行聚合。...简单来说 pivot unpivot列转行 ...
  • 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', ...
  • SQL列转行

    2020-06-17 02:00:41
    student表: 统计各年级各班人数。...原本男女都属于gender字段,一一个值,现在需要把boy,girl作为单独字段输出,即。 通常使用case …when…实现,聚合统计结果作为。 select gradeid,classid, sum
  • lateralview首先为原始表的每调用UDTF,UTDF会把一拆分成一或者多行,lateralview再把结果组合,产生一个支持别名表的虚拟表。select分类字段,concat_ws(’,’,collect_set(合并字段))as别名fromtable_namegroup...
  • SQL 列转行

    千次阅读 2022-07-05 07:28:57
    SQL 学习记录
  • Mysql 列转行

    2022-05-11 14:09:02
    Mysql 列转行
  • --创建一个学生表 create table Stu ( ID int primary key identity, Name varchar(50), Course varchar(50), ...--行专列查询平均成绩 select Name 姓名,Avg(语文) as 语文,avg(数学) as 数学, a
  • sql(PIVOT)与列转行(UNPIVOT)

    千次阅读 2018-07-26 19:43:38
    在做数据统计的时候,列转行是经常碰到的问题。case when方式太麻烦了,而且可扩展性不强,可以使用 PIVOT,UNPIVOT比较快速实现列转行,而且可扩展性强 一、 1、测试数据准备 CREATE ...
  • SQL行汇总 阅读目录 一. 基础语法: 二. 典型实例 一. 基础语法: PIVOT用于将值旋转为列名(即),在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是:PIVOT(聚合函数() FOR ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 409
精华内容 163
关键字:

sql行专列列转行