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

    Unanswered: interesting concat query

    Greetings,

    I have a table with three fields

    x integer,
    y character(20),
    z integer

    Data looks like
    x y z
    1 field 1
    1 field 2
    1 field 3
    2 field1 1
    2 field1 2
    .....

    I'd like a query that in a single select could return me a concatenation of field z, semicolon delimitted, grouped by x and y, is this possible. The query output would like this for the above data:

    1 field 1;2;3
    2 field1 1;2

    Thanks in advance for the help

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Is your database XML enabled

    SELECT x, y,
    VARCHAR(
    REPLACE(
    REPLACE(
    VARCHAR(
    XML2CLOB(
    XMLAGG(
    XMLELEMENT(NAME a, z)
    ORDER BY z)
    ),
    60),
    '<A>', ''),
    '</A>', ';'),
    60) AS z_cat
    from txml group by x,y

    HTH

    Sathyaram

    Quote Originally Posted by lanemax
    Greetings,

    I have a table with three fields

    x integer,
    y character(20),
    z integer

    Data looks like
    x y z
    1 field 1
    1 field 2
    1 field 3
    2 field1 1
    2 field1 2
    .....

    I'd like a query that in a single select could return me a concatenation of field z, semicolon delimitted, grouped by x and y, is this possible. The query output would like this for the above data:

    1 field 1;2;3
    2 field1 1;2

    Thanks in advance for the help
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    Hey, no problem! Try this (change the column names a bit, but that's easy - d1=x, d2=y, emp=z)

    with x (d1, d2, cnt, list, emp, len)
    as (
    select d1, d2, count(*) over (partition by d1, d2),
    cast(char(emp) as varchar(100)), emp, 1
    from test
    union all
    select x.d1, x.d2, x.cnt, x.list ||','|| char(e.emp), e.emp, x.len+1
    from test e, x
    where e.d1 = x.d1 and e.d2 = x.d2
    and e.emp > x.emp
    )
    select d1, d2, list
    from x
    where len = cnt
    order by d1, d2
    ;

    cheers,
    Rob.

Posting Permissions

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