CODEWITHSUNDEEP
sqloracle-database
Devendra S
Devendra S

Reputation: 45

SQL query to display table data as per requirement

Can you please suggest how to display below data as per requirement.

Table (XX) data:

id  type amount
--- ---- -----
1    A     23.0
2    A     12.0
3    A     34.0
4    B     17.0
5    B     16.0
6    B     20.0

Requirement : I want to display output as below

type A amount   type B amount
-------------   -------------
23.0             17.0
12.0             16.0
34.0             20.0

I have tried below query but it is fetching duplicate rows

select a.amount,b.amount 
from xx,(select * from xx where type='A')a,(select * from xx where type='B')b
where xx.id=a.id and xx.id=b.id

Upvotes: 1

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This is a pain. You seem to want to put the values as "lists" in each column. This isn't really the SQL-way, but it is possible:

select max(a.value) as a, max(b.value) as b
from (select xx.*, rownum as seqnum
      from xx
      where type = 'A'
     ) a full outer join
     (select xx.*, rownum as seqnum
      from xx
      where type = 'B'
     ) b
     on a.seqnum = b.seqnum
group by coalesce(a.seqnum, b.seqnum);

You can add order by id to the subqueries if you want to preserve the original ordering.

EDIT:

Note, another way of doing this uses union all:

select max(a) as a, max(b) as b
from ((select rownum as seqnum, value as a, NULL as b from xx where type = 'A'
      ) union all
      (select rownum as seqnum, NULL, value from xx where type = 'B'
      )
     ) ab
group by seqnum;

Upvotes: 2

jarlh
jarlh

Reputation: 44795

If the number of A's always are the same as the number of B's, and the A's always are first:

select ta.amount as "table A amount",
       tb.amount as "table B amount"
from tablename ta
  join tablename tb on ta.id + (select count(*) from tablename) / 2 = tb.id

Upvotes: 1

Related Questions