How Can I Concatenate SQL Query Results in Oracle?

Concatenating Results from SQL Queries in Oracle

To concatenate results from a SQL query in Oracle, you can utilize the LISTAGG function in conjunction with the CONCAT function. This approach allows you to combine values from multiple rows into a single delimited string.

For instance, consider the following table containing data about names and prices:

| NAME | PRICE |
|---|---|
| A | 2 |
| B | 3 |
| C | 5 |
| D | 9 |
| E | 5 |

To display all values in a single row, separated by commas, you can use the following query:

SELECT
  LISTAGG(CONCAT(CONCAT(NAME, ','), PRICE), '|') WITHIN GROUP (ORDER BY NAME) AS CONCATDATA
FROM
  TABLE_NAME;

Here's how this query works:

• The LISTAGG function is used to aggregate concatenated values from each row.
• The CONCAT function combines the NAME and PRICE values into a single string.
• The '|' argument in LISTAGG specifies the delimiter (in this case, a comma) to use between the concatenated values.
• The WITHIN GROUP clause ensures that the concatenation is performed within groups, in this case, grouped by NAME.

The resulting output will be a single row with all the concatenated values separated by commas, as desired:

CONCATDATA
--------------------
A,2|B,3|C,5|D,9|E,5|

The above is the detailed content of How Can I Concatenate SQL Query Results in Oracle?. For more information, please follow other related articles on the PHP Chinese website!