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

This article mainly tells you briefly how to convert the rows and columns of mysql table data. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to convert the rows and rows of mysql table data can bring you some practical help.

1. Row to column

CREATE TABLE `option` (`category_ id` int (10) unsigned NOT NULL COMMENT 'category id', `name` varchar (20) NOT NULL COMMENT' name', KEY `option` (`category_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `option` (`option`, `name`) VALUES

(1, 'big')

(1, 'medium')

(1, 'small')

(2, Mercedes-Benz)

(2, 'BMW')

(3, '2015')

(3, '2016')

(3, '2017')

(3, '2018')

(4,'1m')

(4,'2m'); mysql > select * from `option`

+-+ | category_id | name |

+-+-+ | 1 | large |

| | 1 | medium |

| | 1 | small |

| | 2 | Mercedes Benz |

| | 2 | BMW |

| | 3 | 2015 | |

| | 3 | 2016 | |

| | 3 | 2017 | |

| | 3 | 2018 | |

| | 4 | 1m | | 4 | 2m |

+-+ +

+-+ | category_id | name |

+-+-+ | 1 | large, medium, small |

| | 2 | Mercedes-Benz, BMW |

| | 3 | 2015, 2016, 2017, 2018 | | 4 | 1m, 2m |

+-+ +

Execution result:

Mysql > select category_id,group_concat (name) as name from `option` group by category_id order by category_id

+-+ | category_id | name |

+-+-+ | 1 | large, medium, small |

| | 2 | Mercedes-Benz, BMW |

| | 3 | 2015, 2016, 2017, 2018 | | 4 | 1m, 2m |

+-+ +

two。 Column wrapping

CREATE TABLE `option2` (`option2` (`category_ id` int (10) unsigned NOT NULL COMMENT 'category id', `name` varchar) NOT NULL COMMENT' name set') ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `option2` (`optionid`, `name`) VALUES

(1, 'big, medium, small')

(2, Mercedes-Benz, BMW)

(3, '2015, 2016, 2017, 2018')

Mysql > select * from `option2`

+-+ | category_id | name |

+-+-+ | 1 | large, medium, small |

| | 2 | Mercedes-Benz, BMW |

| | 3 | 2015, 2016, 2017, 2018 | | 4 | 1m, 2m |

+-+ +

+-+ | category_id | name |

+-+-+ | 1 | large |

| | 1 | medium |

| | 1 | small |

| | 2 | Mercedes Benz |

| | 2 | BMW |

| | 3 | 2015 | |

| | 3 | 2016 | |

| | 3 | 2017 | |

| | 3 | 2018 | |

| | 4 | 1m | | 4 | 2m |

+-+ +

Select a.playyplayid.substringedindex (substring_index),',',-1) as name from `option2` as ajoin `option2` as b on b.category_id select a. As b on b.category_id select substringedindex (substring_index),',',-1) as name from `option2` as a-> join `option2` as b on b.category_id order by a.option2`

+-+ | category_id | name |

+-+-+ | 1 | large |

| | 1 | medium |

| | 1 | small |

| | 2 | Mercedes Benz |

| | 2 | BMW |

| | 3 | 2015 | |

| | 3 | 2016 | |

| | 3 | 2017 | |

| | 3 | 2018 | |

| | 4 | 1m | | 4 | 2m |

+-+ +

Mysql table how to achieve the transformation of data rows and columns will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Create test data tables and data

After the rows are transferred to columns, the following results are expected

Row to column, which can be implemented using the group_concat () function in conjunction with group by.

The group_concat () function gets the concatenated value of the combination of expressions. The default delimiter is a comma, which can be set to other delimiters via separator.

Note: the group_concat () function has a limit on the length of the returned result, which defaults to 1024 bytes, but is sufficient for normal situations.

About the use of the group_concat () function, you can refer to my previous article: "mysql function concat and group_concat instructions"

Create test data tables and data

After the column changes rows, the following results are expected

Column wrapping is more complex than row-wrapping. For the data whose column contents are separated by delimiters, we can use the substring_index () function to split the output and combine Cartesian product to realize the loop.

Mysql table how to achieve the transformation of data rows and columns will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.