Is it possible that we pass a query to PIVOT as column list in SQL Server?

Question

As you know the PIVOT syntax is like below :

FROM table_source
PIVOT ( 
 aggregate_function ( value_column )
 FOR pivot_column
 IN ( <column_list>)
) table_alias

I want to know is it possible that we pass a query as <column_list> to PIVOT?

In action, I want to write

FOR DepartmentName IN (SELECT Name From Department))

instead of

FOR DepartmentName IN ([Production], [Engineering], [Marketing]))

Answer 1

The IN list defines the resultset layout.

It should be known at parse time.

Answer 2

If I understand you correctly, NO, you can only do that with dynamic sql.

Answer 3

The only way to have a dynamic "column_list" is to use dynamic sql. You have to build your main query, inject your column list into it and then run it.

SQL Server 2005 Pivot on Unknown Number of Columns