Shulou(Shulou.com)06/01 Report--

In this article Xiaobian for you to introduce in detail the "SQL row column and column transfer method", the content is detailed, the steps are clear, the details are handled properly, I hope this "SQL row transfer column and column transfer method" article can help you solve your doubts, the following follow the editor's ideas slowly in-depth, together to learn new knowledge.

1. Data set

+-+ | id | login_date | +-+-+ | 01 | 2021-02-28 | 01 | 2021-03-01 | 01 | 2021-03-02 | 01 | 2021-03-04 | 01 | 2021-03-05 | 01 | 2021-03-06 | 01 | 2021-03-08 | | 02 | 2021-03-01 | 02 | 2021-03-02 | 02 | 2021-03-03 | 02 | 2021-03-06 | 03 | 2021-03-06 | +-- +-+

Take the data in continuous login as an example:

Select id, concat_ws (',', collect_list (login_date)) cwfrom datagroup by id

Results:

+-+

| | id | cw |

+-+

| | 01 | 2018-02-28, 2018-03-01, 2018-03-02, 2018-03-04, 2018-03-04, 2018-03-05, 2018-03-06, 2018-03-08 |

| | 02 | 2018-03-01, 2018-03-02, 2018-03-03, 2018-03-03, 2018-03-06 |

| | 03 | 2018-03-06 |

+-+

Based on the data generated by SQL above, execute the following SQL:

Select id, login_datefrom t lateral view explode (split (cw,',')) b AS login_date

Results:

+-+ +

| | id | login_date |

+-+ +

| | 01 | 2018-02-28 |

| | 01 | 2018-03-01 |

| | 01 | 2018-03-02 |

| | 01 | 2018-03-04 |

| | 01 | 2018-03-05 |

| | 01 | 2018-03-06 |

| | 01 | 2018-03-08 |

| | 02 | 2018-03-01 |

| | 02 | 2018-03-02 |

| | 02 | 2018-03-03 |

| | 02 | 2018-03-06 |

| | 03 | 2018-03-06 |

+-+ +

After reading this, the article "SQL column conversion and column conversion" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.