Question: I have two tables where the action table may contain multiple records for each device.
I need to test create a query that tests for the presence of only 3 values in the action table and returns those values in separate columns along with the primary table record using an existing Oracle database. 

Here's the table structure: 

**Table\_Device**  

*Objid......desc*  

1..........device1  

2..........device2 

**Table\_Action**  

*ObjiD...Action...Action2Device*  

1............A..........1  

2............B..........1  

3............A..........2  

4............C..........2  

5............D..........2 

(Action2Device equals link to Table\_Device.Objid)

There are many possible actions that can be present but I'm only interested in A,B,and C. I'd like to be able generate a report that resembles the following:

*desc.....ActionA....ActionB...ActionC*  

device1.. .A................B  

Device2...A..............................C

I've managed to create a left join but that repeats the devices for each action record. Any suggestions how to write SQL query to produce this result as shown above?

Answer: 
Have at look at this [SQL Fiddle](http://sqlfiddle.com/#!4/e7e44/2)

No need for a left join, I just used a `CASE` statement to create the additional columns.

```
SELECT D.Name
       ,MAX(CASE WHEN A.Action = 'A' THEN 'A' ELSE NULL END) AS ActionA
       ,MAX(CASE WHEN A.Action = 'B' THEN 'B' ELSE NULL END) AS ActionB
       ,MAX(CASE WHEN A.Action = 'C' THEN 'C' ELSE NULL END) AS ActionC
FROM   TABLE_DEVICE D
INNER JOIN
       Table_Action A
ON     A.Action2Device = D.Objid
GROUP BY
       D.Name

```
In Oracle 11g,you can also do this.

```
with t as 
(select  a.device,b.action
 from table_device a join table_action b 
 on a.objid=b.action2device)
select * 
from t
pivot (MAX(ACTION)
       for action
       in ('A' AS ACTIONA,'B' AS ACTIONB,'C' AS ACTIONC)) 

```

<http://www.sqlfiddle.com/#!4/39f97/11>