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

愛好特長：

1、愛好和特長有很多，擅長word、excel、ppt、wind等軟件的安裝與卸載；

2、精通PE、PS、PB、DCF、PEG等單詞的拼寫；

3、熟悉Windows、Linux、Mac、Android、IOS、WP8等系統(tǒng)的開關(guān)機。

今日重點：

???① 花里胡哨、不擇手段的sql寫法；

???② 一個深度回答，把面試官征服。

白天工作晚上寫文，嘔心瀝血整理，有問題歡迎評論，點贊、收藏、評論是對我最大的支持！！！

目錄

一 緣起

二 火花

2.1 內(nèi)置函數(shù)實現(xiàn)行轉(zhuǎn)列

2.2?經(jīng)典case when實現(xiàn)

2.3 Python groupby 實現(xiàn)列轉(zhuǎn)行

2.4?Python pandas 實現(xiàn)列轉(zhuǎn)行

2.5?execl 數(shù)據(jù)透視表實現(xiàn)行轉(zhuǎn)列

2.6 Java 實現(xiàn)行轉(zhuǎn)列

2.7 hive sql實現(xiàn)行轉(zhuǎn)列

2.8?Teradata UDF實現(xiàn)行轉(zhuǎn)列

三 闌尾

---

一 緣起

在我們熱愛的《數(shù)據(jù)倉庫交流群》里發(fā)生了一幕：

：請教大佬們 這個咋搞？

? ? ? ? ? ? ? 那9個字，猶如一聲驚雷，在這個熱情的群里炸開了鍋...

：大佬們紛紛按奈不住（尤其是我），看不得別人有問題，我心急如焚，一是擔(dān)心這位小哥哥面試受挫，二是這么好的學(xué)de習(xí)se的機會，我一定要把握住。。

大佬1： 這位來自上海的大佬，首先給出了orcale自帶函數(shù)的解法...

?大佬2： 基于orcale自帶函數(shù)的局限性，大佬2提出了適用于mysql、oracle、hive的case when寫法...

?大佬3： hive 高級函數(shù)（花里胡哨起來了）...

?

問題：

ps.哈哈哈哈，這不就是10次面試9次問的行轉(zhuǎn)列嘛~

討論過程中：

大佬們紛紛諫言獻策，集思廣益。

?

二 火花

2.1 內(nèi)置函數(shù)實現(xiàn)行轉(zhuǎn)列

原理

SELECT * FROM student PIVOT (SUM(score) FOR subject IN (語文, 數(shù)學(xué), 英語) ) #默認(rèn)按照score和subject以外其它字段進行g(shù)roup by

結(jié)果展示

2.2?經(jīng)典case when實現(xiàn)

使用case when來依條件分列是最簡單的一種方法。

select?學(xué)生號,?? sum(case?科目?when?'語文'?then?成績?end)?as?語文,?? sum(case?科目?when?'數(shù)學(xué)'?then?成績?end)?as?數(shù)學(xué),?? ......?? from?table?? where?...?? group?by?學(xué)生號??

case when進階，動態(tài)列數(shù)行轉(zhuǎn)列：

但是，轉(zhuǎn)換后列數(shù)不固定的情況下呢？對照上面的例子來說就是，假設(shè)我并不知道學(xué)生學(xué)了哪些科目的時候應(yīng)該怎么做？

我們繼續(xù)用case when，但是由于列的不固定，需要先根據(jù)條件分支產(chǎn)生的可能性來拼接一下語句再動態(tài)執(zhí)行。直接上碼看效果可能更清晰一點：

declare?@sql?varchar(4000)?? set?@sql?=?'select?學(xué)生號'??select?@sql?=?@sql?+?',sum(case?科目?when?'''+科目+'''?then??成績?end)?['+科目+']'?? from?(select?distinct?科目?from?table??)?as?a?? order?by?科目??select?@sql?=?@sql+'?from?table??group?by?學(xué)生號'??--print?@sql?? exec(@sql)?

2.3 Python groupby 實現(xiàn)列轉(zhuǎn)行

df_new = df_new.groupby(by='電影名', as_index=False).agg("/".join)print(df_new)

2.4?Python pandas 實現(xiàn)列轉(zhuǎn)行

import pandas as pddf = pd.DataFrame([['夏洛特?zé)?#39;,'沈騰/馬麗/尹正/艾倫/王智'],['縫紉機樂隊','大鵬/喬杉/古力娜扎/李鴻其/韓童生']],columns=['電影名','部分演員']) print(df)# 根據(jù)'/'拆分為列表 df['部分演員'] = df['部分演員'].str.split("/") # 轉(zhuǎn)成列表 print(df)df_new = df.explode('部分演員') print(df_new)

2.5?execl 數(shù)據(jù)透視表實現(xiàn)行轉(zhuǎn)列

Excel 要實現(xiàn)行列轉(zhuǎn)換，需要用到 Power Query，而Power Query 只有Excel 2016以上的版本才有！

第一步，【Ctrl A 全選數(shù)據(jù)】-【數(shù)據(jù)】-【從表格】，創(chuàng)建 Power Query，進入 Power Query界面。

第二步，添加輔助列。Excel 實現(xiàn)列轉(zhuǎn)換，可以通過【添加輔助列】來實現(xiàn)該效果。而在 Power Query 有多種可以添加輔助列的方法。此處介紹兩種方法法一，通過自定義列，添加輔助列法二，通過重復(fù)列，實現(xiàn)添加輔助列

第三步，進行透視列。【透視列】>【值列，自定義，選中需要透視的列】-【聚合值函數(shù)，選擇不要聚合】-【確定】。

第四步，合并列。選中透視出來的列，右鍵，【合并列】-【自定義分隔符】-【確定】 。

最后，選中多余的列，刪除！再進行【關(guān)閉并上載】。全部搞定！列轉(zhuǎn)行后的數(shù)據(jù)

ps. execl實現(xiàn)的原文鏈接：https://zhuanlan.zhihu.com/p/315340716

2.6 Java 實現(xiàn)行轉(zhuǎn)列

public class Row2Line {public static void main(String[] args) throws IntrospectionException, IllegalArgumentException, IllegalAccessException, InvocationTargetException { //你提供的對象列表，需要轉(zhuǎn)換的原數(shù)據(jù) List<StudentGrand> StudentGrandList = getStudentGrandList(); //實現(xiàn)行轉(zhuǎn)列的算法List<List<String>> convertedTable = convert(StudentGrandList);//打印轉(zhuǎn)換后的集合，查看結(jié)果print(convertedTable); //剩下的可以根據(jù)實際需求，將轉(zhuǎn)換好的集合傳給前端、或隨意處理} private static List<List<String>> convert(List<StudentGrand> StudentGrandList) throws IntrospectionException, IllegalAccessException, InvocationTargetException {//取得StudentGrand的屬性，當(dāng)然你也可以用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++) {//每一行的第一列對應(yīng)StudentGrand字段名 //所以新table的第一列要設(shè)置為字段名if(i == 0){ rowLine.add(field.getName()); } //新table從第二列開始，某一列的某個值對應(yīng)舊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; }//測試用數(shù)據(jù)，實際應(yīng)該從數(shù)據(jù)庫查詢，傳過來的private static List<StudentGrand> getStudentGrandList () { List<StudentGrand> list = new ArrayList<StudentGrand>();list.add(new StudentGrand("001", "toni", "語文", "98"));list.add(new StudentGrand("001", "toni", "數(shù)學(xué)", "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", "數(shù)學(xué)", "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", "安東尼", "數(shù)學(xué)", "98"));list.add(new StudentGrand("003", "安東尼", "外語", "98"));list.add(new StudentGrand("003", "安東尼", "體育", "98"));return list; } //打印查看結(jié)果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實現(xiàn)的原文鏈接：https://blog.csdn.net/LRXmrlirixing/article/details/100163550

2.7 hive sql實現(xiàn)行轉(zhuǎn)列

使用函數(shù)：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實現(xiàn)行轉(zhuǎn)列

還是壓縮到一行，不如case when的展示效果

REPLACE FUNCTION tdstats.UDFCONCAT(aVarchar VARCHAR(128) CHARACTER SET UNICODE)RETURNS VARCHAR(10000) CHARACTER SET UNICODECLASS AGGREGATE (20000)SPECIFIC udfConcatLANGUAGE CNO SQLNO EXTERNAL DATAPARAMETER STYLE SQLNOT DETERMINISTICCALLED ON NULL INPUTEXTERNAL NAME 'SL!staudf!F!udf_concatvarchar'

三 闌尾

剩下992種包含python、java、C， if else實現(xiàn)之類的方法，請大家自行腦補，歡迎在評論區(qū)發(fā)表你的高見~

?

求點贊、求評論、求收藏！！

總結(jié)

以上是生活随笔為你收集整理的建议收藏丨sql行转列的一千种写法！！的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。