This is a follow-up to this FAQ:

When using an array type variable to build the IN list for a PIVOT clause, it would be handy to specify aliases for the generated columns.

For IN with a constant expression, I can add the alias with an AS clause, such as

   ...
   FOR DepartmentID IN
      (100 as "Dept. 100", 200 as "Dept. 200", 300 as "Dept. 300", 400 as "Dept. 400")

and get columns like "Dept. 100_TotalSalary" in the result set.

How do I do that when using "IN variable"?

I've tried to use an array of aliased values, such as the following, but that fails because the whole field value (e.g. '100 as Dept100') seems to used as pivot value, returning no matches. (Note that I had to cast DepartmentID to varchar in the pivot-source-table, probably due to the same issue...)

CREATE VARIABLE @varAliasedDepartments varchar(20) ARRAY;
SET @varAliasedDepartments =
   (SELECT ARRAY_AGG(DISTINCT DepartmentID || ' as Dept' || DepartmentID ORDER BY DepartmentID) FROM Employees);
SELECT * 
FROM ( SELECT cast(DepartmentID as varchar) as DepartmentID, State, Salary 
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
     ) MyPivotSourceData
   PIVOT ( 
      SUM( Salary ) TotalSalary  
      FOR DepartmentID IN @varAliasedDepartments
   ) MyPivotedData
ORDER BY State;