DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: CONCAT strings from multiple rows in one SELECT

  1. #1
    Join Date
    Jul 2001
    Posts
    2
    I'd like to be able to return a single cell containing a string formed from all the records in a table, ie

    mysql> SELECT OrganisationName FROM t1;
    +------------------+
    | OrganisationName |
    +------------------+
    | this |
    | that |
    | the other |
    +------------------+

    I want this:

    +-----------------------+
    | SomeSelectFunction |
    +-----------------------+
    | this, that, the other |
    +-----------------------+

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Probably will have to use a stored function. Something like:
    Code:
    declare
      lAnswer varchar2(2222);
    begin
      for x in (select rownum, OrganisationName from t1) loop
        if x.rownum = 1 then
          lAnswer := x.OrganisationName;
        else
          lAnswer := lAnswer || ', ' || x.OrganisationName;
        end if;
      end loop;
      return lAnswer;
    end;
    /
    Jeff Hunter

  3. #3
    Join Date
    Jul 2001
    Posts
    2
    No other choice to get it done with a single querry

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sure, by using DECODE or CASE, but only if you know in advance how many records there are in a table...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width