建议收藏丨sql行转列的一千种写法！！_不吃西红柿丶_sql行转列

大大的周 02-07 4558

大家好，我是明月十四桥！！

爱好特长：

1、爱好和特长有很多，擅长word、excel、ppt、wind等软件的安装与卸载；

2、精通PE、PS、PB、DCF、PEG等单词的拼写；

3、熟悉Windows、Linux、Mac、Android、IOS、WP8等系统的开关机。

今日重点：

???① 花里胡哨、不择手段的sql写法；

???② 一个深度回答，把面试官征服。

白天工作晚上写文，呕心沥血整理，有问题欢迎评论，点赞、收藏、评论是对我最大的支持！！！

一缘起

二火花

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 内置函数实现行转列

原理

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

结果展示

2.2?经典case when实现

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

select?学生号,?? sum(case?科目?when?'语文'?then?成绩?end)?as?语文,?? sum(case?科目?when?'数学'?then?成绩?end)?as?数学,?? ......?? from?table?? where?...?? group?by?学生号??

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

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

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

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 实现列转行 df_new = df_new.groupby(by='电影名', as_index=False).agg("/".join) print(df_new) 2.4?Python pandas 实现列转行 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 实现行转列 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))? 压缩到一行，跟题目要求稍有差异。

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的展示效果

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实现之类的方法，请大家自行脑补，欢迎在评论区发表你的高见~

求点赞、求评论、求收藏！！