How to display multiple lines as one line in SQL

Tips for combining multiple rows of data into one row in SQL include: using the CONCAT function to connect strings, such as dividing names into full names. Use the GROUP_CONCAT function to connect the values ​​of all rows in a group, such as grouping the product list. Use the STRING_AGG function to connect strings and specify delimiters, such as grouping skill lists and adding delimiters. Use the FOR XML PATH('') clause to convert the query results to an XML string. Use the STUFF function to insert substrings, such as converting the customer name to a line with an XML tag.

Tips for displaying multiple lines as one line in SQL

In SQL queries, glueing multiple rows of data into one row can improve readability and manageability. There are several ways to do this, and here are some of the most commonly used ones:

1. CONCAT function

CONCAT() function can concatenate multiple strings. For example, to join first_name , middle_name and last_name columns in the table customer to a row, you can use the following query:

 <code class="sql">SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name FROM customer;</code>

2. GROUP_CONCAT function

GROUP_CONCAT() function concatenates the values ​​of all rows in a group into a string. It is usually used with GROUP BY clause. For example, to group and display product_name columns in the table order in each row, you can use the following query:

 <code class="sql">SELECT product_category, GROUP_CONCAT(product_name) AS product_list FROM order GROUP BY product_category;</code>

3. STRING_AGG function

STRING_AGG() function is an enhanced version of the GROUP_CONCAT() function, which allows for more flexibility in conjunction of strings. For example, to group skill columns in table employee and display them as delimited lists, you can use the following query:

 <code class="sql">SELECT department, STRING_AGG(skill, ', ') AS skill_set FROM employee GROUP BY department;</code>

4. FOR XML PATH('')

The FOR XML PATH('') clause can convert the query results to XML format and then display it as a single-line string. For example, to convert all rows in a table invoice into a single row XML string, you can use the following query:

 <code class="sql">SELECT * FROM invoice FOR XML PATH('');</code>

5. STUFF function

STUFF() function can insert substrings of a specified length into an existing string. It is usually used in conjunction with FOR XML PATH('') clause to insert XML tags. For example, to convert first_name and last_name columns in the table customer to rows with XML tags, you can use the following query:

 <code class="sql">SELECT STUFF(( SELECT ',<name>' first_name '</name>' FROM customer FOR XML PATH('') ), 1, 1, '<customer>') AS customer_list;</customer></code>

The above is the detailed content of How to display multiple lines as one line in SQL. For more information, please follow other related articles on the PHP Chinese website!