Is it possible to submit a query to PIVOT as a column in SQL Server?

Question

As you know, the syntax is PIVOTas follows:

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

I want to know if it is possible to transfer the request as <column_list>to PIVOT?

In action I want to write

FOR DepartmentName IN (SELECT Name From Department))

instead

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

Answer 1

The list INdefines the layout of the results.

It must be known during parsing.

Answer 2

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

Answer 3

The only way to have dynamic "column_list" is to use dynamic sql. You must create your main query, insert your list of columns into it, and then run it.

SQL Server 2005 basics on an unknown number of columns