Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Unanswered: Concatenating multiple rows into a single row

    I would like a query that returns a single row with a column that is a concatenation of data from multiple rows. For example:

    Data
    1 | 181
    1 | 158
    1 | 430
    2 | 614
    2 | 622

    I would want as a result:

    1 | 181, 158, 430
    2 | 614, 622

    It would be nice to be able to specify the separator (comma used as an example).

    Is this possible using Oracle SQL or would it require PL/SQL?

    Thanks for the help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Concatenating multiple rows into a single row

    This is not trivial! But a solution is described here:

    http://asktom.oracle.com/pls/ask/f?p...:2196162600402

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I had the same problem as you and this is the solution I came up with using just SQL and no PLSQL. But it does require 9i : (.

    The query uses the analytic functions to allow it create a linked list of data. The linked list is then converted to a tree using CONNECT BY. And finally it uses SYS_CONNECT_BY_PATH to create your list of values before grouping the data. The query may require tuning of indexes and or hints depending on the volume and distribution of data.


    Query to generate a list of values from a child table (in CSV format) for every primary key value in a parent table.

    i.e.

    if parent table has

    id
    1
    2
    3

    and the child table has

    id value_id
    1 a
    1 b
    1 c
    2 d
    3 e
    3 f

    this query will output

    id list of value_ids
    1 a,b,c
    2 d
    3 e,f


    The only issue I can think of is what happens if the list is longer than the varchar2 limit of 4000 bytes.

    Obviously this only works with 9i due to the SYS_CONNECT_BY_PATH : (.



    -- get the longest csv list of values for each id
    select
    id,
    SUBSTR(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),2) as lov
    from
    (
    -- create a tree and use sys_connect_by_path to create a csv list of values
    select id , SYS_CONNECT_BY_PATH(value_id, ',') list, level lev
    from
    (
    -- create a result set with a linked list column for every id so that we can create a hierarchical tree with the next query
    select r.id, i.value_id, rownum prow,
    LAG(rownum, 1) OVER (PARTITION BY i.id ORDER BY value_id) as connect_id
    from parent_table r, child_table i
    where r.id = i.id
    ) r
    START WITH connect_id is null
    CONNECT BY PRIOR prow=connect_id
    )
    group by id
    order by id


    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •