前言
今日重点：
① 花里胡哨、不择手段的sql写法；
② 一个深度回答，把面试官征服。
正文
一 缘起
在我们热爱的《数据仓库交流群》里发生了一幕：
：请教大佬们 这个咋搞？
那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实现之类的方法，请大家自行脑补，欢迎在评论区发表你的高见~
