Is there a way to construct a PIVOT's IN clause using a SELECT?

Question

I want to build a PIVOT table, but I may have to use dynamic SQL unless the IN clause can be constructed in regular SQL. I want to do something like this:

SELECT *
FROM
(
    SELECT x, y, z...
) srcTable
PIVOT
(
    MIN(srcTable.TimesDownloaded) FOR OrganizationName IN (SELECT CONCAT('Download_', OrganizationName) FROM AdamUser GROUP BY OrganizationName)
) pivotTable

So when I SELECT unique OrganizationNames from AdamUser and prefix Download_, that gives me a table whose row values equal the names of the columns I want. This is invalid syntax though. Do I always have to hard-code the PIVOT column names?

Answer 1

The T-SQL documentation of the FROM clause has this for the specification:

<pivoted_table> ::=  
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]  

<pivot_clause> ::=  
        ( aggregate_function ( value_column [ [ , ]...n ])   
        FOR pivot_column   
        IN ( <column_list> )   
    )

<column_list> ::=  
    column_name [ ,...n ]   

---

So no, it doesn't allow you to select the column names with a SELECT statement, and yes, you have to "hard-code" the PIVOT column names.