What is a simple way to combine grouped values in one field?

Question

I mean:

Table PHONE_CODES:

ID CODE_NAME PHONE_CODE 
1   USA         8101
2   USA         8102
3   PERU        8103
4   PERU_MOB    81031
5   PERU_MOB    81032

And I want via select to get something like this:

CODE_NAME   ZONE_CODES
USA          8101; 8102;
PERU         8103
PERU_MOB     81031; 81032;

I could get it via the function below, but perhaps there is a better way:

select distinct(CODE_NAME) as CODE_NAME, get_code_names_by_ZONE(CODE_NAME) as ZONE_CODES from PHONE_CODES;

Function:

create or replace function get_code_names_by_ZONE
(
    ZONE_CODE_NAME in varchar2
)
return varchar2
as
    codes_list varchar2(4000);
    cursor cur_codes_list is
        select p.PHONE_CODE
        from PHONE_CODES p
        where p.CODE_NAME = ZONE_CODE_NAME;
begin

    for codes_list_rec in cur_codes_list
    LOOP
        -- dbms_output.put_line('PHONE_CODE:[' || codes_list_rec.PHONE_CODE || ']');
        codes_list := codes_list || codes_list_rec.PHONE_CODE || '; ';
    end loop;

    return codes_list;

    EXCEPTION 
        when NO_DATA_FOUND then
            return 'notfound';
        WHEN others then
            dbms_output.put_line('Error code:' || SQLCODE || ' msg:' || SQLERRM);
            return null;
end get_code_names_by_ZONE;
/

Answer 1

A function would be my preferred method of achieving what you want.

Answer 2

Tim Hall has an excellent discussion on the various string aggregation techniques that are available in Oracle.

Answer 3

If you're on 11g, take a look at the new PIVOT extension to SQL - the best documentation looks to be in the Data Warehousing Guide section. I believe however that the target of the "... for in ..." clause cannot be a subquery and has to be a hard-coded list of values.

Answer 4

Another option is to use some third party database tool that is good for grouping, this one can group your data by any combination of fields you desire.

Answer 5

Good link Justin. Tim hall is awesome. I followed his advice and here it is:

  1  SELECT CODE_NAME,
  2     LTRIM(MAX(SYS_CONNECT_BY_PATH(PHONE_CODES,';'))
  3     KEEP (DENSE_RANK LAST ORDER BY curr),';') AS PHONE_CODES
  4  FROM   (SELECT CODE_NAME,
  5      PHONE_CODES,
  6      ROW_NUMBER() OVER (PARTITION BY CODE_NAME ORDER BY PHONE_CODES) AS curr,
  7      ROW_NUMBER() OVER (PARTITION BY CODE_NAME ORDER BY PHONE_CODES) -1 AS prev
  8      FROM   a)
  9  GROUP BY CODE_NAME
 10  CONNECT BY prev = PRIOR curr AND CODE_NAME = PRIOR CODE_NAME
 11* START WITH curr = 1
SQL> /

CODE_NAME  PHONE_CODES
---------- --------------------------------------------------
PERU       8103
PERU_MOB   81031;81032
USA    8101;8102

dbBradley - I don't think the Pivot extension works here. The Pivot extension requires the use of an aggregate (sum, count, ...).