Menu
My inputs are in this way.
col1 col2 1 a 1 b 2 c 2 d 2 e O/p: Should Be like
col1 col2 1 a,b 2 c,d,e I want a query that can be fired at DB level. I've tried various ways, but wasn't able to make this out...
967
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.
About LISTAGG. The LISTAGG function is used to aggregate a set of string values within a group into a single string by appending the string-expression values based on the order that's specified in the 'WITHIN GROUP' clause. As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row ...
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
11g and higher: Use listagg:
SELECT col1, LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names" FROM table_x GROUP BY col1 10g and lower: One method is to use a function:
CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val in number) RETURN VARCHAR2 IS return_text VARCHAR2(10000) := NULL; BEGIN FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP return_text := return_text || ',' || x.col2 ; END LOOP; RETURN LTRIM(return_text, ','); END; / To use the function:
select col1, get_comma_separated_value(col1) from table_name Note: There is an (unsupported) function WM_CONCAT available on certain older versions of Oracle, which might help you out - see here for details.
In MySQL:
SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1 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
