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 x, y, z...
) srcTable
    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?

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.