in order to pivot table within sql, suppose the following Occupation table contain 3 feature i.e. ID, FirstName, and Occupation, as follow:

[![Occupation Table](https://i.stack.imgur.com/kyziZ.png)](https://i.stack.imgur.com/kyziZ.png)

then in order to return pivot base on its occupation i tried the following:

```
select actress,
       Dancer,
       Photographer,
       Salesman
       from Occupation o 
       pivot (max(o.FirstName) for o.Occupation in ([Actress], [Dancer], [Photographer], [Salesman])) as pivotable

```

[![Pivot](https://i.stack.imgur.com/ooczC.png)](https://i.stack.imgur.com/ooczC.png)

While the result return NULL. What suppose to achieve in here was the pivot only contain Name of Person base on their Occupation without return all Null, as following table

[![Target Result](https://i.stack.imgur.com/BHpRk.png)](https://i.stack.imgur.com/BHpRk.png)