建议收藏丨sql行转列的一千种写法！！

2022-07-29 10:20:01 浏览数 (1)

目录

一 缘起

二 火花

2.1 内置函数实现行转列

2.2 经典case when实现

2.3 Python groupby 实现列转行

2.4 Python pandas 实现列转行

2.5 execl 数据透视表实现行转列

2.6 Java 实现行转列

2.7 hive sql实现行转列

2.8 Teradata UDF实现行转列

三 阑尾

一 缘起

在我们热爱的《数据仓库交流群》里发生了一幕：

：请教大佬们 这个咋搞？

              那9个字，犹如一声惊雷，在这个热情的群里炸开了锅...

：大佬们纷纷按奈不住（尤其是我），看不得别人有问题，我心急如焚，一是担心这位小哥哥面试受挫，二是这么好的学de习se的机会，我一定要把握住。。

大佬1： 这位来自上海的大佬，首先给出了orcale自带函数的解法...

大佬2： 基于orcale自带函数的局限性，大佬2提出了适用于mysql、oracle、hive的case when写法...

大佬3： hive 高级函数（花里胡哨起来了）...

问题：

ps.哈哈哈哈，这不就是10次面试9次问的行转列嘛~

讨论过程中：

大佬们纷纷谏言献策，集思广益。

二 火花

2.1 内置函数实现行转列

原理

代码语言：javascript复制
SELECT *
FROM student
PIVOT (
 SUM(score) FOR subject IN (语文, 数学, 英语)
)   #默认按照score和subject以外其它字段进行group by

结果展示

2.2 经典case when实现

使用case when来依条件分列是最简单的一种方法。

代码语言：javascript复制
select 学生号,  
sum(case 科目 when '语文' then 成绩 end) as 语文,  
sum(case 科目 when '数学' then 成绩 end) as 数学,  
......  
from table  
where ...  
group by 学生号  

case when进阶，动态列数行转列：

但是，转换后列数不固定的情况下呢？对照上面的例子来说就是，假设我并不知道学生学了哪些科目的时候应该怎么做？

我们继续用case when，但是由于列的不固定，需要先根据条件分支产生的可能性来拼接一下语句再动态执行。直接上码看效果可能更清晰一点：

代码语言：javascript复制
declare @sql varchar(4000)  
set @sql = 'select 学生号'  
  
select @sql = @sql   ',sum(case 科目 when ''' 科目 ''' then  成绩 end) [' 科目 ']'  
from (select distinct 科目 from table  ) as a  
order by 科目  
   
select @sql = @sql ' from table  group by 学生号'  
  
--print @sql  
exec(@sql) 

2.3 Python groupby 实现列转行

代码语言：javascript复制
df_new = df_new.groupby(by='电影名', as_index=False).agg("/".join)

print(df_new)

2.4 Python pandas 实现列转行

代码语言：javascript复制
import pandas as pd

df = pd.DataFrame([['夏洛特烦恼','沈腾/马丽/尹正/艾伦/王智'],
                   ['缝纫机乐队','大鹏/乔杉/古力娜扎/李鸿其/韩童生']],
                  columns=['电影名','部分演员'])
print(df)

# 根据'/'拆分为列表
df['部分演员'] = df['部分演员'].str.split("/")      # 转成列表
print(df)

df_new = df.explode('部分演员')
print(df_new)

2.5 execl 数据透视表实现行转列

Excel 要实现行列转换，需要用到 Power Query，而Power Query 只有Excel 2016以上的版本才有！

第一步，【Ctrl A 全选数据】-【数据】-【从表格】，创建 Power Query，进入 Power Query界面。

第二步，添加辅助列。Excel 实现列转换，可以通过【添加辅助列】来实现该效果。而在 Power Query 有多种可以添加辅助列的方法。此处介绍两种方法法一，通过自定义列，添加辅助列法二，通过重复列，实现添加辅助列

第三步，进行透视列。【透视列】>【值列，自定义，选中需要透视的列】-【聚合值函数，选择不要聚合】-【确定】。

第四步，合并列。选中透视出来的列，右键，【合并列】-【自定义分隔符】-【确定】 。

最后，选中多余的列，删除！再进行【关闭并上载】。全部搞定！列转行后的数据

ps. execl实现的原文链接：https://zhuanlan.zhihu.com/p/315340716

2.6 Java 实现行转列

代码语言：javascript复制
public class Row2Line {
 
    public static void main(String[] args) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {  
        //你提供的对象列表，需要转换的原数据 
        List<StudentGrand> StudentGrandList = getStudentGrandList();  
        //实现行转列的算法
        List<List<String>> convertedTable = convert(StudentGrandList);
        //打印转换后的集合，查看结果
        print(convertedTable);  
        //剩下的可以根据实际需求，将转换好的集合传给前端、或随意处理
    }  
  
    private static List<List<String>> convert(List<StudentGrand> StudentGrandList)  
            throws IntrospectionException, IllegalAccessException, InvocationTargetException {//取得StudentGrand的属性，当然你也可以用list = {"id", "name", ...}  
        Field[] declaredFields = StudentGrand.class.getDeclaredFields();
          
        List<List<String>> convertedTable = new ArrayList<List<String>>();  
          
        //多少个属性表示多少行，遍历行  
        for (Field field : declaredFields) {
            field.setAccessible(true);  
            ArrayList<String> rowLine = new ArrayList<String>();
            //list<T>多少个StudentGrand实体类表示有多少列，遍历列
            for (int i = 0, size = StudentGrandList.size(); i < size; i  ) {
                //每一行的第一列对应StudentGrand字段名  
                //所以新table的第一列要设置为字段名
                if(i == 0){  
                    rowLine.add(field.getName());  
                }  
                //新table从第二列开始，某一列的某个值对应旧table第一列的某个字段
                else{  
                    StudentGrand StudentGrand = StudentGrandList.get(i);  
                    String val = (String) field.get(StudentGrand);//grand为int会报错
                    System.out.println(val);
                    rowLine.add(val);  
                }  
            }  
            convertedTable.add(rowLine);  
        }  
        return convertedTable;  
    }
    //测试用数据，实际应该从数据库查询，传过来的
    private static List<StudentGrand> getStudentGrandList () {  
        List<StudentGrand> list = new ArrayList<StudentGrand>();
        list.add(new StudentGrand("001", "toni", "语文", "98"));
        list.add(new StudentGrand("001", "toni", "数学", "98"));
        list.add(new StudentGrand("001", "toni", "外语", "98"));
        list.add(new StudentGrand("001", "toni", "体育", "98"));
        list.add(new StudentGrand("006", "amy", "语文", "98"));
        list.add(new StudentGrand("006", "amy", "数学", "98"));
        list.add(new StudentGrand("006", "amy", "外语", "98"));
        list.add(new StudentGrand("006", "amy", "体育", "98"));
        list.add(new StudentGrand("003", "安东尼", "语文", "98"));
        list.add(new StudentGrand("003", "安东尼", "数学", "98"));
        list.add(new StudentGrand("003", "安东尼", "外语", "98"));
        list.add(new StudentGrand("003", "安东尼", "体育", "98"));
        return list;  
    }  
    //打印查看结果
    private static void print(List<List<String>> convertedTable) {
        //String json = JSONArray.formObject(convertedTable).toString();
        for (List<String> list : convertedTable) { 
            for (String string : list) {
                System.out.print(string "  ");  
            }  
            System.out.println();  
        }  
    }

ps. java实现的原文链接：https://blog.csdn.net/LRXmrlirixing/article/details/100163550

2.7 hive sql实现行转列

使用函数：concat_ws(',',collect_set(column))  压缩到一行，跟题目要求稍有差异。

代码语言：javascript复制
select user_id,
concat_ws(',',collect_list(order_id)) as order_value 
from col_lie
group by user_id

2.8 Teradata UDF实现行转列

还是压缩到一行，不如case when的展示效果

代码语言：javascript复制
REPLACE FUNCTION tdstats.UDFCONCAT
  (aVarchar VARCHAR(128) CHARACTER SET UNICODE)
 RETURNS VARCHAR(10000) CHARACTER SET UNICODE
 CLASS AGGREGATE (20000)
 SPECIFIC udfConcat
 LANGUAGE C
 NO SQL
 NO EXTERNAL DATA
 PARAMETER STYLE SQL
 NOT DETERMINISTIC
 CALLED ON NULL INPUT
 EXTERNAL NAME 'SL!staudf!F!udf_concatvarchar'

三 阑尾

剩下992种包含python、java、C， if else实现之类的方法，请大家自行脑补，欢迎在评论区发表你的高见~

hive python linux java

0 人点赞