Is there any function in oracle similar to group_concat in mysql? [duplicate]

by

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

More Related Contents:

  1. How do I limit the number of rows returned by an Oracle query after ordering?
  2. When do I need to use a semicolon vs a slash in Oracle SQL?
  3. LISTAGG in Oracle to return distinct values
  4. Using ‘case expression column’ in where clause
  5. Oracle SQL escape character (for a ‘&’)
  6. SQL Error: ORA-00933: SQL command not properly ended
  7. CASE vs. DECODE
  8. Convert a string date into datetime in Oracle
  9. DATEDIFF function in Oracle [duplicate]
  10. WHERE IN condition not accepting String value
  11. What is the difference between Views and Materialized Views in Oracle?
  12. Pivoting of data using two columns
  13. Check if current date is between two dates Oracle SQL
  14. Benefits Of Using SQL Ordinal Position Notation?
  15. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  16. Unordered results in SQL
  17. Oracle Dynamic Pivoting
  18. create custom function for date difference excluding weekends and holidays in oracle sql
  19. What is it exactly a BLOB in a DBMS context
  20. Custom Order in Oracle SQL
  21. How do I list all tables in a schema in Oracle SQL?
  22. oracle sql: update if exists else insert [duplicate]
  23. Compare Strings ignoring accents in SQL (ORACLE)
  24. Search an Oracle database for tables with specific column names?
  25. How to replace specific values in a oracle database column?
  26. ORA-01861: literal does not match format string
  27. Query a Table’s Foreign Key relationships
  28. Backup SQL Schema Only?
  29. IN vs OR of Oracle, which faster?
  30. is there a PRODUCT function like there is a SUM function in Oracle SQL?

Leave a Comment