Results 1 to 4 of 4

Thread: qryhlp

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: qryhlp

    create table craj (col1 varchar2(10))

    insert into craj values('A')

    insert into craj values('b')

    insert into craj values('c')



    i want a quiry which returns

    ABC

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    Funcion

    As far as I know this can't be done with sql, you'll have to create a function, one way would be:

    1 CREATE OR REPLACE FUNCTION rows_to_string
    2 RETURN VARCHAR2
    3 AS
    4 l_string varchar2(4000);
    5 BEGIN
    6 FOR i IN (select * from craj) LOOP
    7 l_string := l_string || i.col1;
    8 END LOOP;
    9 RETURN l_string;
    10* END rows_to_string;
    [SYS@DB2.WORLD:NL-UTH-L057656] $ /

    Function created.

    [SYS@DB2.WORLD:NL-UTH-L057656] $ SELECT rows_to_string FROM dual;

    ROWS_TO_STRING
    -------------------------------------------------------------------
    abc

    Goodl uck!
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Sep 2002
    Posts
    10
    There is a way in SQL, but at a cost. And this query only works for a fixed number of rows. The idea is to select the smallest value from the table and then concatenate that value with the next highest row/value in the same table. If you know there are only 3 rows in this table, the query would be something like....

    || is my notation for concatenate fields

    select a.col1||b.col1||c.col1 from craj a
    join craj b on b.col1 = (select min(b1.col1) from craj b1



    create table craj (col1 varchar2(10))

    insert into craj values('A')

    insert into craj values('b')

    insert into craj values('c')



    i want a quiry which returns

    ABC

  4. #4
    Join Date
    Sep 2002
    Posts
    10
    I apologize....hit the post button by accident.....

    There is a way in SQL, but at a cost. And this query only works for a fixed number of rows. The idea is to select the smallest value from the table and then concatenate that value with the next highest row/value in the same table. If you know there are only 3 rows in this table, the query would be something like....

    || is my notation for concatenate fields

    select a.col1||b.col1||c.col1 from
    craj a, craj b, craj c
    where a.race = (select min(a1.race) from craj a1) -- start with smallest
    and b.race = (select min(b1.race) from craj b1
    where b1.race > a.race --next higher
    and c.race = (select min(c1.race) from craj c1
    where c1.race > b.race --next higher

    This is really an inefficient query though, if you execute alot, I'd
    look at other options.

Posting Permissions

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