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...
- Do you want the query to work both in Oracle and in MySQL?Yasir Arsanukayev– Yasir Arsanukayev2013-05-27 10:26:31 +00:00Commented May 27, 2013 at 10:26
- 4possible duplicate of this or this or this or this or ...A.B.Cade– A.B.Cade2013-05-28 06:38:38 +00:00Commented May 28, 2013 at 6:38
- 1...or thisBohemian– Bohemian ♦2018-12-04 01:04:03 +00:00Commented Dec 4, 2018 at 1:04
Add a comment |
1 Answer
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 
1 Comment
albfan
LISTAGG will throw a "ORA-01489: result of string concatenation is too long" if result exceeds VARCHAR2 limits. See stackoverflow.com/a/29008456/848072 for custom aggregate function