Results 1 to 5 of 5

Thread: inserting comma

  1. #1
    Join Date
    Nov 2011
    Posts
    25

    Unanswered: inserting comma

    create table ebe.test (id int, name varchar(2));
    insert into ebe.test values (1,'a');
    insert into ebe.test values (1,'b');
    insert into ebe.test values (1,'b');
    insert into ebe.test values (2,'x');
    insert into ebe.test values (2,'x');
    insert into est values (2,'y');
    I have this data
    I am getting out put as
    1 a b
    2 x y
    using this query
    select id,
    xmlquery ('distinct-values($x//row)' passing xmlgroup (name) as "x") as c1
    from ebe.test
    group by id
    But i want the output as
    1 a,b
    2 x,y
    How to tweak the above the query to get the desired output.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    Try listagg.

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    25
    Quote Originally Posted by ARWinner View Post
    Try listagg.

    Andy
    With Listagg it is not possible I cannot make it distinct.

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Try this:

    Code:
    select id, listagg(name, ',')
    from (
    select distinct id, name
    from ebe.test
    )
    group by id
    Regards,
    Mark.

  5. #5
    Join Date
    Nov 2011
    Posts
    25
    Mark- Honestly speaking that will not work in my solution.

    can you help me how we execute this query to get the output as

    1 a,b
    2 x,y
    select
    xmlquery('for $z in $m return fn:replace($z," " ,",")' passing c1 AS "m")
    from
    (
    select id,
    --replace(xmlquery ('distinct-values($x//row)' passing xmlgroup (name) as "x") , ' ' , ',' ),
    xmlquery ('distinct-values($x//row)' passing xmlgroup (name) as "x") as c1
    from febe.test
    group by id)

Posting Permissions

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