1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to iterate through table in sql?

Discussion in 'SQL PL/SQL' started by Mr.777, Mar 21, 2011.

  1. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    Hello everyone....

    Actually i am working over a problem and to fullfil that i need to do following:
    1. Get all the data depending upon some condition from the table.
    2. And with each row it should give it's row count.
    e.g. 1. a b c d
    2. e f g h

    If a b c d and e f g h are columns values.

    I tried using row_count but all in vain... Can anyone tell me how may i do this...
    I am just allowed to use query (sql)
    Urgent help will be greatly appreciated.


    Thanks..
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
  3. ora

    ora Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    SQL> select * from alpha;

    C
    -
    A
    B
    C
    D
    E
    F
    E
    F
    G
    H
    Z
    H
    Z
    G
    Z

    15 rows selected.

    SQL> desc alpha
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    COL VARCHAR2(1)

    SQL> select count(col), col from alpha group by col order by col;

    COUNT(COL) C
    ---------- -
    1 A
    1 B
    1 C
    1 D
    2 E
    2 F
    2 G
    2 H
    3 Z

    9 rows selected.

    SQL>declare
    2 a number;
    3 a1 number;
    4 b varchar2(100);
    5 b1 varchar2(10);
    6 cursor c is select count(col), col from alpha group by col order by col;
    7 begin
    8 open c;
    9 fetch c into a,b;
    10 loop
    11 fetch c into a1,b1;
    12 exit when c%notfound;
    13 if a=a1 then
    14 b:=b||' '||b1;
    15 else
    16 dbms_output.put_line(a||' '||b);
    17 a:=a1;
    18 b:=b1;
    19 end if;
    20 end loop;
    21 dbms_output.put_line(a||' '||b);
    22 close c;
    23* end;
    SQL> /
    1 A B C D
    2 E F G H
    3 Z

    PL/SQL procedure successfully completed.


    Regards,...
     
  4. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    Thanks for all your help but i am not allowed to use pl/sql :-(

    Just structured query language....

    Here is the query...

    Code (SQL):
    SELECT what_here???(i wrote ROW_COUNT but ALL IN vain),tl.Name,tl.Company,tl.STATUS
                       FROM abc tl, def ts
                       WHERE tl.id=ts.id;
     
     
  5. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    Actually i am using reports... And now i've got idea to use Formula Column....
    I try to write
    Code (SQL):
    FUNCTION CF_1FORMULA0011 RETURN NUMBER IS
    i NUMBER :=0;
    BEGIN
        i:=i+1;
        RETURN i;
    END;
     
    in Formula Column but it kept giving errors...
    Then one of my senior told me to use summary column inside the query and use count function at report level....

    I really thanks alot to you guys that you helped me alot but actually i realize that i was wrong... Sorry to all of you and thanks as well....

    Next time i'll be more specific regarding this...
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Thi is one of the several ways of doing it.

    Code (SQL):
    SQL> SELECT * FROM alpha;

    C
    -
    A
    B
    C
    D
    E
    F
    E
    F
    G
    H
    Z

    C
    -
    H
    Z
    G
    Z

    15 ROWS selected.

    SQL> SELECT COUNT(col), col FROM alpha GROUP BY col ORDER BY col;

    COUNT(COL) C
    ---------- -
             1 A
             1 B
             1 C
             1 D
             2 E
             2 F
             2 G
             2 H
             3 Z

    9 ROWS selected.

    SQL> SELECT CNT, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL,' ')),' ') str FROM (
      2  SELECT COUNT(COL) CNT, COL,
      3         ROW_NUMBER() OVER (PARTITION BY COUNT(COL) ORDER BY COL) CURR,
      4         ROW_NUMBER() OVER (PARTITION BY COUNT(COL) ORDER BY COL)-1 PREV
      5   FROM SCOTT.ALPHA GROUP BY COL ORDER BY COL)
      6  GROUP BY CNT
      7  CONNECT BY PREV = PRIOR CURR AND CNT = PRIOR CNT
      8  START WITH CURR = 1;

           CNT STR
    ---------- -------------------------
             1 A B C D
             2 E F G H
             3 Z

    SQL>
     
  7. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    Thanks for your reply...
    First, i've been solved the problem as problem was something else. My wrong description about problem and i am sorry for that :)
    Second, This will assign serial numbers to the group but i wanted to do this per record, so here it fails :)
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please explain the problem in brief so that somebody may help you.
    Also t give us the table data and proposed output.
     
  9. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    I have been told above...... Just before your post... And i have found the solution that i've been described.... Thanks for your support