I have a table with the following format:

DB_KEY     DB_VALUE
------     --------
row1       value1
row2;1     value2
row2;2     value3
row3       value4
row4;1     value5
row4;2     value6
row4;3     value7

I want to return only the DB_VALUE column, and if the first part of the DB_KEY (before the semicolon) is the same on any rows then merge the rows together. So the return for this example above would be:

value1
value2value3
value4
value5value6value7

I thought to do something with group by, but it seems like that can't be used if the column you are grouping on is not selected. Ie:

SELECT DB_VALUE FROM TABLE GROUP BY REXEP_SUBSTR(DB_KEY, ';', 1, 1)

Any suggestions would be greatly appreciated.

• sql
• oracle
• group-by

You can do something like this

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select 'row1' db_key, 'value1' db_value from dual union all
  3    select 'row2;1', 'value2' from dual union all
  4    select 'row2;2', 'value3' from dual union all
  5    select 'row3', 'value4' from dual union all
  6    select 'row4;1', 'value5' from dual union all
  7    select 'row4;2', 'value6' from dual union all
  8    select 'row4;3', 'value7' from dual
  9  )
 10  select key,
 11         max( sys_connect_by_path(db_value, ' '))
 12             keep( dense_rank last order by curr ) db_value
 13    from (select key,
 14                 db_value,
 15                 row_number() over (partition by key
 16                                        order by db_value) curr,
 17                 row_number() over (partition by key
 18                                        order by db_value) - 1 prev
 19            from (select substr( db_key,
 20                                 1,
 21                                 (case when instr( db_key, ';' ) = 0
 22                                       then length(db_key)
 23                                       else instr( db_key, ';' ) - 1
 24                                   end)) key,
 25                          db_value
 26                     from x))
 27     group by key
 28   connect by prev = PRIOR curr and key = prior key
 29*    start with curr = 1
SQL> /

KEY                      DB_VALUE
------------------------ ------------------------------
row1                      value1
row2                      value2 value3
row3                      value4
row4                      value5 value6 value7

You can also take a look at Tim Hall's page listing various string aggregation techniques in Oracle. If you are using Oracle 11.2 or you can create functions or types, you should be able to produce a simpler query that produces the same output.

If you are willing to create an Oracle "group_concat" aggregate function, then you could use it on your query to get the desired result. If you search for AskTom and group concat you will find the definitions all over. Here is one example: http://kb.yarmakconsulting.com/2008/06/oracle-analog-of-mysql-groupconcat.html

I'd imagine you could do this with a hierarchical query as well.

You can achieve this using WM_CONCAT, but not everyone approves of this function You may use some other concatenating function instead of WM_CONCAT, which is available in newer version of oracle.

SELECT DISTINCT (SELECT DISTINCT NVL(WM_CONCAT(DB1.DB_VALUE), DB.DB_VALUE)
                   FROM DB_BUFFER DB1
                  WHERE SUBSTR(DB1.DB_KEY, 0, INSTR(DB1.DB_KEY, ';') - 1) =
                        SUBSTR(DB.DB_KEY, 0, INSTR(DB.DB_KEY, ';') - 1)) AS DB_VALUE
  FROM DB_BUFFER DB

This will check for the start of the DB_KEY part(before ;). In case it matches it will join all the values in to one string.