sql : select query without duplicate data

Question

select ft.id, ft.qty, st.data from first_table ft
left outer join second_table st on ft.id = st.id
where ft.id = '1abc'

I have two tables. Schemas of those tables are as follows

No 1 table schema

Id	Desc	Qty
1abc	One	3
1abc	two	6

No 2 table schema

Id	Data
1abc	12ab
1abc	23ab
1abc	z99c

The data show wrongly after I join query

Id	Desc	Qty	Data
1abc	One	3	12ab
1abc	two	6	12ab
1abc	One	3	23ab
1abc	two	6	23ab
1abc	One	3	z99c
1abc	two	6	z99c

Following is the correct way it should show in the crystal report. Please suggest me how should I do.

Id	Qty	Data
1abc	3	12ab
	6	23ab
		z99c

Answer 1

You seem to want to list values in separate columns. This is usually best done in the application layer, but you can also use SQL:

select min(id), min(qty), min(data)
from ((select id, null as qty, null as data,
              row_number() over (order by id) as seqnum
       from table1
       group by id
      ) union all
      (select null as id, qty, null as data,
              row_number() over (order by qty) as seqnum
       from table1
       group by qty
      ) union all
      (select null as id, null as qty, data,
              row_number() over (order by data) as seqnum
       from table2
       group by data
      )
     ) x
group by seqnum;