First of all, after applying PIVOT, all columns of the pivoted set must be referenced using the alias specified for the PIVOT clause. As per the manual:
When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.
So, if everything else was correct, it would need to be
from dept d
inner join emp e
Alternatively, you could omit the alias altogether since in this case you are not joining the pivoted set to other datasets and so all columns available would be only those coming from
However, one other problem is that you are referencing a column that is no longer available after the pivot: your
dept_name column is used to produce columns
[pROD],[elect],[inst],[fin],[otherdepartment] and itself does not exist in the result set.
You could try selecting all columns (
*) to see the output. I suspect that you will find that your output is not what you expect, because you likely have too many grouping columns coming from the result of the join between
emp. How you should resolve that may need to be asked as a separate question, though.