Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL how to convert column data into row data". In daily operation, I believe that many people have doubts about how to convert column data into row data in MySQL. Xiaobian consulted all kinds of information and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "how to convert column data into row data by MySQL". Next, please follow the editor to study!

The developer asked me to help me see how to record a data.

The original data is as follows, which should be entered into the database.

Cancel the cell merge of Excel first.

Delete the first and second rows of data, these are headings.

Then export the CSV file

Open CSV using a text file editor, replace-for

And then Detabes built a watch.

Then import the CSV data.

Insert into target (channel,d,program,starttime,endtime)

Select 'Voice of China', 1 djinmin (starttime), max (endtime) from

SELECT @ gid: = @ cgid, @ cgid: = t.d1, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d1 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by D1 rank

Union all

Select 'Voice of China', 2meme d2min (starttime), max (endtime) from (

SELECT @ gid: = @ cgid, @ cgid: = t.d2, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d2 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d2 rank

Union all

Select 'Voice of China', max (endtime) from (

SELECT @ gid: = @ cgid, @ cgid: = t.d3, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d3 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d3 rank

Union all

Select 'Voice of China', max (endtime) min (starttime)

SELECT @ gid: = @ cgid, @ cgid: = t.d4, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d4 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d4 rank

Union all

Select 'Voice of China', 5 djinmin (starttime), max (endtime) from (

SELECT @ gid: = @ cgid, @ cgid: = t.d5, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d5 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d5 rank

Union all

Select 'Voice of China', 6 minutes (starttime), max (endtime) from

SELECT @ gid: = @ cgid, @ cgid: = t.d6, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d6 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d6 rank

Union all

Select 'Voice of China', 7 mins (starttime), max (endtime) from (

SELECT @ gid: = @ cgid, @ cgid: = t.d7, if (@ gid = @ cgid, @ rank,@rank: = @ rank + 1) AS rank, t.* from

(select id,starttime,endtime,d7 from t order by id) t, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 0) T2

) T4 group by d7 rank

View the result

This method looks like there are many steps, but it is actually very easy.

The main work is to import Excel into the database, merge after import, and use the same SQL to modify it. There's not much work.

Otherwise, using JAVA parsing, you also need to merge your own time periods. It is not an easy thing either.

At this point, the study on "how MySQL converts column data into row data" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.