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

pivotsql serversql-server-2012t-sql

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

Best Answer

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.

Related Solutions

Sql-server – Statement could not be prepared

There are a couple JOIN combinations that seem abnormal to me.

For example:

left outer join dbo.vwQuoteStatus 
     inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

vs.

left outer join dbo.vwQuoteStatus 
                appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
) 
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
) 
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.

Best Answer

Related Solutions

Sql-server – Statement could not be prepared

Sql-server – Query intermittently fails with “The multi-part identifier could not be bound”

Related Question