3

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
4

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.

  • thank you for looking into the querry.Here i want to find out no. of employees in each department so departemnt name is coming from Dept table and no. of emplyees is coming from emp table by counting theire Id.I tried adding the fix you gave here but now i am getting an error asMsg 207, Level 16, State 1, Line 1 Invalid column name 'dept_name'. – Priyanka Oct 10 '15 at 12:51
  • Meanwhile what approach you will take for this querry plzz suggest thank you for same :) – Priyanka Oct 10 '15 at 12:55
  • Yes, I know that dept_name is an invalid reference, I've said that in my answer. From your explanation, I guess you probably don't need PIVOT at all, a simple GROUP BY query would seem enough. – Andriy M Oct 10 '15 at 18:30
  • I am using pivot here because I am practising how to implement pivot . I want to print all the department names as column names and no. of employee working in each department as its record – Priyanka Oct 12 '15 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.