CODEWITHSUNDEEP
sqlmultiple-columnsoracle12c
Skywalker
Skywalker

Reputation: 77

Show SQL result in separate columns

I want to show this table where trading_area and reporting_unit are shown in separate columns for each id_num and name_item.

id_num name_item trading_area reporting_unit
1001 YC Washington BOA
1002 NO Utah BCG
1001 GO Arizona NYP

I'm using the following query:

SELECT item.id_number, item.name_item, inf.info_value   
FROM traded_item item, item_info inf, item_info_types typ
WHERE item.id_number = inf.item_id
AND inf.info_type_id = typ.type_id
AND typ.type_id IN (20005, 20001) ---trading_area, reporting_unit 
ORDER BY name

However, this shows me the following table:

id_num name_item info_value
1001 YC Washington
1002 NO Utah
1001 GO Arizona
1001 YC BOA
1002 NO BCG
1001 GO NYP

Table schema

Upvotes: 1

Views: 873

Answers (1)

lemon
lemon

Reputation: 15482

First of all you should make JOIN operations explicit, using the JOIN keyword and the ON clause that specifies the matching conditions.

Then you can use two CASE expressions to extract your values, and the MAX aggregation function to remove your NULL values.

SELECT item.id_number, 
       item.name_item, 
       MAX(CASE WHEN typ.type_id = 20005 THEN inf.info_value END) AS trading_area,
       MAX(CASE WHEN typ.type_id = 20001 THEN inf.info_value END) AS reporting_unit 
FROM       traded_item     item
INNER JOIN item_info       inf
        ON item.id_number = inf.item_id
INNER JOIN item_info_types typ
        ON inf.info_type_id = typ.type_id
       AND typ.type_id IN (20005, 20001)
GROUP BY item.id_number, 
         item.name_item
ORDER BY name

Upvotes: 1

Related Questions