CODEWITHSUNDEEP
sqloracle-database
Anji007
Anji007

Reputation: 133

Selecting the data from a table by grouping one column in Oracle

I have a table T1 as shown below 

ID PASSED   FAILED  PASSED_PERCENTAGE   FAILED_PERCENTAGE
-- ------   ------  -----------------   -----------------
1  380      172     50                  23

I need a SQL query which gives me the output as below

DESC           ID        TOTAL         PERCENTAGE
----           --        -----         ----------
PASSED         1         380           50
FAILED         1         172           23

Note: Table T1 will always have only one row and the column ID will have constant value always as '1' Could anyone please help me with this. Many thanks in advance.

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Oracle 12C supports lateral joins which is an improvement over subqueries (the table is only scanned once):

select x.*
from t cross join lateral
     (select 'Passed' as which, id, passed as total, passed_percentage as percentage
      from dual
      union all
      select 'Failed' as which, id, failed as total, failed_percentage as percentage
      from dual
     ) x;

Of course, for this sample data sample, scanning is not a performance issue -- unless the table is really a view that is quite complicated.

However, lateral joins are very powerful and worth learning about.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use union all

select 'Passed' as 'desc', id, PASSED as total,PASSED_PERCENTAGE as PERCENTAGE
from tablename
union all
select 'Falied', id, Failed,FAILED_PERCENTAGE
from tablename

Upvotes: 2

Related Questions