Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: oracle 10g - string concat question

    Hi Everyone,

    Having an issue and can't figure out how to solve it.
    Say I have a dataset like the following
    Code:
    id --- last name --- first name --- result --- extra
    1          A               A          80       prof.A
    1          A               A          80       school A
    1          A               A          80       city A
    1          A               A          80       Country A
    2          B               B          70       school B
    2          B               B          70       prof B
    2          B               B          70       city B
    2          B               B          70       Country B
    what I want to do is combine that extra column into 1
    which I figured a way by using wm_concat
    Code:
    select a.id,a.lastname,a.firstname,b.result,wm_concat(b.extra) as extra
    from tables users a,info b
    where a.id = b.id
    group by id,lastname,firstname,result
    this will return the data nicely like so:
    Code:
    id --- last name --- first name --- result --- extra
    1          A              A           80        prof.A,School A, City A, Cou A
    2          B              B           70        schoolB,profB,cityB,country b
    My problem is I want to order by extra field first so that it comes in the same order (prof,school,city,country) there is a sequence field in table b that each of the 4 values are associated to (1=prof,2=school,3=city,4=country) so how can I return this data so the extra field will be in the same order for all results?
    if I try adding a order by to the above query then the wm_concat doesn't work and I get the proper order but everything is on seperate rows.

    Thanks in advance.

  2. #2
    Join Date
    Oct 2011
    Location
    Hamburg, Germany
    Posts
    18
    you can write your own function. i think in 10g there no buildin function to aggregate strings.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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