You have to enumerate the dates you want to include e.g.
select * from
( select staffid, effectivedate from #EvaluationDetail) src
pivot
( count(staffid) for effectivedate in ([2018-01-10], [2018-03-10], [2018-02-05]) ) piv;
Note I've changed the function to
count
because the average of a staffid means nothing at all.
I've assumed that the column
effectivedate
is declared as type
date
- specifically not
datetime
!
If you want that list of dates to be determined by the data itself then you will need some dynamic SQL.