query solution req.

[trust]

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

[Shestakov]

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.

[tamilselvan]

How about using PIVOT table?
I have not tried, I just throw the concept.