Mysql uses group_concat () to merge multiple rows of data into one row, and mysqlgroup_concat

Source: Internet
Author: User

Mysql uses group_concat () to merge multiple rows of data into one row, and mysqlgroup_concat

Assume that two tables a, B, and B are associated with table a through the field id, and table a and table B are one-to-many relationships. Assume that table B has a field name. Now you need to query the records in Table a and obtain the name information stored in Table B according to the regular query, the number of rows to be displayed. If you want to display only the number of rows in Table a, you need to merge the multiple rows in the query name field. This can be achieved through a program, but it can also be completed directly at the SQL layer.

Method:

Using the group_concat () method, the parameter is the field to be merged. The default delimiter of the merged field is comma, which can be specified by the separator parameter. This method is often used together with group.

Example:

Select a. *, group_concat (B. name separator '-') as name from a left join B on a. id = B. id group by a. id;

Mysql merges multiple rows of data (based on the primary key)

Perform row-to-column Conversion Based on your second query. If the type is dynamic, we recommend that you encapsulate the stored procedure. Set a query on the outer layer, which can be implemented by using a query like max (case when then... end) as 'column name.
 
Merge multiple SQL rows into one row

Processing of converting multiple rows into one row.
Different databases have different processing mechanisms.

Use for xml for SQL Server 2005 +
MySQL uses the GROUP_CONCAT function for processing.
Oracle uses the WMSYS. WM_CONCAT function for processing

Other databases have not been studied ......
Reference: hi.baidu.com/...a.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.