Hi Tom,
Thanks for quick response. But now the problem is that data is in same table.
Scenario:

I've a table containing thousands of records of three types.(A,B & C).
Actually, scenario is this that my table can have zero or multiple rows of 'B' & 'C' type for each row of Type 'A'.
Scripts are given below.

CREATE TABLE AB (col1 number(3), col2 number(3), col3 varchar2(5), col4 number(5));

INSERT INTO AB values (1,2,'A',3000);
INSERT INTO AB values (1,2,'B',3000);
INSERT INTO AB values (1,3,'A',3000);
INSERT INTO AB values (1,3,'C',2000);
INSERT INTO AB values (2,1,'A',4000);
INSERT INTO AB values (2,1,'B',3000);
INSERT INTO AB values (2,1,'B',1000);
INSERT INTO AB values (3,1,'A',5000);
INSERT INTO AB values (3,1,'C',3000);
INSERT INTO AB values (3,1,'C',1000);
INSERT INTO AB values (3,1,'C',1000);

Now I want my output in form as

Col1 Col3 Col4
---- ---- ----
1 A 3000
B 3000
C 2000
2 A 4000
B 3000
B 1000
C -
3 A 5000
B -
C 3000
C 1000
C 1000

Please help.

thanks,
Jitin

You know.... one wonders why this wasn't the original question - since the original question was so very different from this!

Your output is confusing - why isn't A repeated in your output for col1 = 1?  (or why is C repeated when col1 = 3)

ops$tkyte@ORA9IR2> with
  2  data as
  3  (select distinct col1 from ab where col3 = 'A'),
  4  abc as
  5  (select 'A' col3 from dual union all
  6   select 'B' col3 from dual union all
  7   select 'C' col3 from dual ),
  8  abc_data as
  9  (select * from data, abc)
 10  select abc_data.col1, abc_data.col3, ab.col4
 11    from abc_data left join ab on (abc_data.col1 = ab.col1 and abc_data.col3 = ab.col3)
 12   order by abc_data.col1, abc_data.col3
 13  /

      COL1 C       COL4
---------- - ----------
         1 A       3000
         1 A       3000
         1 B       3000
         1 C       2000
         2 A       4000
         2 B       3000
         2 B       1000
         2 C
         3 A       5000
         3 B
         3 C       3000
         3 C       1000
         3 C       1000

13 rows selected.
 

Hi tom,
Thanks for the solution.
I think, I was not able to explain my requirement properly. I'm sorry for that.

I got the same solution Using CASE and PipeLined Function Split as given below.

SELECT
LS.COL1,
LS.ITEMS,
CASE
WHEN LS.ITEMS = AB.COL3 THEN
AB.COL4
ELSE
NULL
END COL3
FROM
(
SELECT * FROM
(SELECT DISTINCT COL1 FROM AB) EF,
(SELECT * FROM TABLE(SPLIT('A,B,C'))) GH
) LS,
AB
WHERE LS.COL1 = AB.COL1(+)
AND LS.ITEMS = AB.COL3(+)
ORDER BY LS.COL1
--I used Split function here instead of Using 'select from dual' to make it more Dynamic though I think it'll be an overhead?

output:
COL1 C COL4
---------- - ----------
1 A 3000
1 A 3000
1 B 3000
1 C 2000
2 A 4000
2 B 3000
2 B 1000
2 C
3 A 5000
3 B
3 C 3000
3 C 1000
3 C 1000

But actual requirement is...
'A' type record is a master record and one set of records is identified by value of Col1.
i.e. Records with value 1 in col1 is a set in which 'A' is master record while 'B' & 'C' are child records. 'A' record is reconciled (e.g.reconcilation of invoices and payments) with 'B' type record and has value in col2 different than value in col2 when 'A' record is reconciled with 'C' type record.
In last set of my records, I got the payment ('C' type record) in three parts against one invoice ('A' type record.) -- Please check numeric sum of 'A' & 'C' records.

My requirement is to show all payments for a single invoice.

Hope, this time, I made myself clear..Sorry for so much of trouble but you are the only one I can ask.

Is it possible to solve this thing or do u think that there is some flaw in design?

Thanks again for help,
jitin

My query was not at all a refinement.It cannot be. I just asked you about overhead in my query using Split Function as I found that cost of query by you was lesser.

Secondly, its not exactly Master-detail relationship as there are no references made on columns. I just meant that like master-detail records, 'A' type records are used as 'Base Records' and are not supposed to be repeated in output while 'B' & 'C' type records can be multiple.