1

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?

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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