Pivot Table Error 56901 non-constant expression

Question

I need to create a pivot table of average salary in Division_ID as Row and Job_ID as Column using data in employees2 table. So far I have:

SELECT * 
FROM (
SELECT Division_ID, Salary, Job_ID
FROM Employees2
)
PIVOT (
AVG(Salary) FOR Job_ID IN (ENG, TEC, MGR)
)
ORDER BY Job_ID

I get the error, "non-constant expression is not allowed for pivot|unpivot values"

What am I doing wrong? Most the examples I can find online deal with Numerical values after the IN statement, do I need to do something different since im working with text/string?

Answer 1

Use below Query.

SELECT *    FROM (
    SELECT Division_ID, Salary, Job_ID
    FROM Employees2
)  
PIVOT 
(
     AVG(Salary) FOR Job_ID IN 
    ('ENG', 'TEC', 'MGR')     ------------> Change done here.....
)
ORDER BY Job_ID

The values ENG, TEC and MGR need to be surrounded by single quotes to be considered as constant values.