sqloracle-database

Selecting from 1 table and put the result into 2 column

I have to select from a table and put into a 2 column result.

This is the table I have,

MAILTABLE:

MAIL_KIND CODE PRICE
PRIORITY MAILCOST 20
REGULAR MAILCOST 10

So from trying out the exercise above,

I have the following SQL statement:

select 
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'PRIORITY') as PM,
  (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'REGULAR') as RM
from MAILTABLE

But I'm getting a bunch of result which is not what I want is to differentiate the PRIORITY mail cost and the REGULAR mail cost like below:

PM RM
20 10

How do I select from 1 table and put into a 2 column result?

Solution

  • Leave out FROM MAILTABLE at the end. That causes it to return the values of the subqueries for each row in the original table.

    Use FROM DUAL as a dummy table when you don't need a specific table in the main query.

    select (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'PRIORITY') as PM,
       (select PRICE from MAILTABLE where CODE = 'MAILCOST' AND MAIL_KIND = 'REGULAR') as RM
FROM DUAL