It is recommended to collect a thousand ways to write sql row to column! !

Hello everyone, I am Mingyue Fourteen Bridge! !
hobbies:
1. There are many hobbies and specialties, good at installing and uninstalling software such as word, excel, ppt, wind, etc.;
2. Proficient in the spelling of words such as PE, PS, PB, DCF, PEG, etc.;
3. Familiar with the power on and off of Windows, Linux, Mac, Android, IOS, WP8 and other systems.
Today's focus:
① Fancy, unscrupulous sql writing;
② An in-depth answer to conquer the interviewer.
I work and write articles at night during the day and work hard to organize. If you have any questions, please comment. Likes, favorites , and comments are my greatest support! ! !

table of Contents

One origin

Two sparks

2.1 Built-in function to realize row conversion

2.2 Implementation of classic case when

2.3 Python groupby implements column to row

2.4 Python pandas implements column conversion

2.5 execl pivot table to achieve row to column

2.6 Java implements row-to-column

2.7 Hive SQL realizes row conversion

2.8 Teradata UDF realizes row conversion

Three appendix


One origin

A scene happened in our beloved "Data Warehouse Exchange Group":

: Ask the big guys how to do this?

Those 9 words, like a thunder, exploded in this enthusiastic group...

: The big guys can’t help it (especially me). I don’t see other people have problems. I am anxious. One is worried that this little brother will be frustrated in the interview, and the other is such a good opportunity to learn se. I must grasp it. live. .

Big guy 1: This big guy from Shanghai first gave the solution to orcale's own function...

Boss 2: Based on the limitations of orcale's own functions, Boss 2 proposes a case when writing method suitable for mysql, oracle, hive...

Gangster 3: hive advanced functions (the bells and whistles are up)...

Question :

ps. Hahahaha, isn’t this just the line-up of 10 interviews and 9 questions ~

During the discussion :

The big guys all gave advice and suggestions and brainstormed.

Two sparks

2.1 Built-in function to realize row conversion

principle

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

Result display

2.2 Implementation of classic case when

Using case when to sort by conditions is the easiest way.

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

Case when advanced, dynamic column number row to column:

However, what if the number of columns after conversion is not fixed? In contrast to the above example, what should I do when I don’t know what subjects the student has studied?

We continue to use case when, but because the columns are not fixed, we need to splice the statements according to the possibility of conditional branching and then dynamically execute them. It may be clearer to directly upload the code to see the effect:

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 implements column to row

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

2.4  Python pandas implements column conversion

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 pivot table to achieve row to column

Excel needs to use Power Query to achieve row and column conversion, and Power Query is only available in Excel 2016 and above!

The first step , [ Ctrl A select all data ]-[ data] - [from table ], create Power Query, enter the Power Query interface.

The second step is to add auxiliary columns. Excel realizes column conversion, and this effect can be achieved through [Add Auxiliary Column]. In Power Query, there are many ways to add auxiliary columns. Two methods are introduced here. One is to add an auxiliary column by customizing the column. Method two is to add an auxiliary column by repeating the column.

The third step is to make a perspective column. [ Perspective column ]>[Value column, custom, select the column that needs perspective]-[aggregate value function, choose not to aggregate]-[OK].

The fourth step is to merge the columns. Select the perspective column, right click, [ Merge Columns ]-[ Custom Separator ]-[ OK ].

Finally, select the extra column and delete it! Then proceed to [ Close and Upload ]. All done! Data after column conversion

ps. The link to the original text implemented by execl: https://zhuanlan.zhihu.com/p/315340716

2.6 Java implements row-to-column

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. The original link of java implementation: https://blog.csdn.net/LRXmrlirixing/article/details/100163550

2.7 Hive SQL realizes row conversion

Use function: concat_ws(',',collect_set(column)) to compress to one line, which is slightly different from the requirements of the title.

select user_id,concat_ws(',',collect_list(order_id)) as order_value from col_liegroup by user_id

2.8 Teradata UDF realizes row conversion

Still compressed to one line, not as good as the display effect of 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'

Three appendix

The remaining 992 methods including python, java, C, if else implementation, etc., please make up your own brains, and welcome to post your opinions in the comment area~

Ask for likes, comments, and collections! !