The multi-part identifier “d.dept_name” could not be bound

Question

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp table.

I am getting this error for my code

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "d.dept_name" could not be bound.

This is my query:

SELECT d.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab

Answer 1

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

SELECT pivotab.dept_name
from dept d
inner join emp e
on e.dept_ID=d.ID

Pivot
(
  count(e.ID)
  for d.dept_Name
  in([pROD],[elect],[inst],[fin],[otherdepartment])

  )
  as pivotab

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 pivotab.

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 dept and emp. How you should resolve that may need to be asked as a separate question, though.