I have a table that looks like this:
A 1 A 2 B 1 B 2
And I want to produce a result set that looks like this:
A 1 2 B 1 2
Is there a SQL statement that will do this? I am using Oracle.
Related questions:
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
Listagg operates on a group of rows and returns as a single column with user-defined concatenate character. In the above example, I used a comma (,). You can use a comma, semi-colon or any valid character.
COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL . If all occurrences of expr evaluate to null, then the function returns null. Oracle Database uses short-circuit evaluation.
Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.
(WARNING - WM_CONCAT
is an unsupported function that was removed in version 12c. Unless you're using a very old database, you should avoid this function. You should probably use LISTAGG
instead.)
It depends on the version of Oracle you're using. If it supports the wm_concat() function, then you can simply do something like this:
SELECT field1, wm_concat(field2) FROM YourTable GROUP BY field2;
wm_concat() basically works just like group_concat() in MySQL. It may not be documented, so fire up ye olde sqlplus and see if it's there.
If it isn't there, then you'll want to implement something equivalent yourself. You can find some instructions on how to do this in the string aggregation page at oracle-base.com.
Pretty old topic, but it could help others since Oracle improved in the mean time.
The LISTAGG function is what you are looking for (in 11g at least)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With