query solution req.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: query solution req.

  1. #1
    Join Date
    Jan 2003
    Posts
    1

    query solution req.

    Hi All,

    Imagine a table having
    Columns

    1. Col-1
    2. Col-2
    3. Col-3

    data is like this

    col-1 col-2 col-3
    1 x1 y1
    1 x2 y2
    1 x3 y3
    2 x1 y3
    2 x4 y0


    I want output in this way through QUERIES ONLY

    1 x1 y1 x2 y2 x3 y3
    2 x1 y3 x4 y0

    Help is appreciated...


    Reg,

    kunal

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Code:
    SQL> create or replace type test_list as object
    ( 
      a number,
      b varchar2(30)
    );
    /
    Type created.
    
    SQL> create or replace type tb_list as table of test_list;
    /
    Type created.
    
    SQL> create table test_tbl_list
    as 
    select ceil(rownum/3) rn, object_id, object_name
    from all_objects where rownum < 6;
    
    Table created.
    
    SQL> select         
      a.rn, 
      cast(multiset(select b.object_id   a, 
                           b.object_name b
                     from test_tbl_list b
                     where b.rn = a.rn) as tb_list) lst
    from (select distinct rn rn from test_tbl_list) a
    ;
    	RN LST(A, B)
    ---------- --------------------------------------------------------------------------------------------------------------
    	 1 TB_LIST(TEST_LIST(89, 'ACCESS$'), TEST_LIST(1283, 'ALL_ALL_TABLES'), TEST_LIST(1244, 'ALL_ARGUMENTS'))
    	 2 TB_LIST(TEST_LIST(1506, 'ALL_ASSOCIATIONS'), TEST_LIST(1162, 'ALL_CATALOG'))
    In any other cases u have to write addition function on pl/sql for build list of values.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How about using PIVOT table?
    I have not tried, I just throw the concept.

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