Selecting from 1 table and put the result into 2 column

November 14, 2024

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