How to create a table view by joining two tables without overlapping records with same relation id

Question

I have a table to store all agent activities like below,

+----+---------+-------------+-------------+
| id | subject |    type     | relation_id |
+----+---------+-------------+-------------+
|  1 | Call    | lead        |          25 |
|  2 | Visit   | Opportunity |          25 |
+----+---------+-------------+-------------+

How is it possible to make a view like this.

+----+---------+-----------+------------------+
| id | Subject |   lead    |   Opportunity    |
+----+---------+-----------+------------------+
|  1 | Call    | lead_name |                  |
|  2 | Visit   |           | Opportunity_name |
+----+---------+-----------+------------------+

Answer 1

You need to use CASE for getting required result as:

SELECT id,subject,
CASE WHEN type='lead' THEN 'lead_name' ELSE '' END as lead,
CASE WHEN type='Opportunity' THEN 'Opportunity_name' ELSE '' END as Opportunity
FROM mytable

Answer 2

$this->db->join('Opportunity','Opportunity.id = store_all_agent.relation_id','left');
$query = $this->db->get('store_all_agent');
return $query->result_array();

refer : https://www.codeigniter.com/userguide2/database/active_record.html