Dynamic pivot columns without dynamic SQL statement?

Question

I'm just wondering if there's any way to do a pivot query, with dynamic column names, without resorting to dynamic sql (declare @sql_text varchar(max) = 'select ...' etc.)

Dynamic SQL just rubs me the wrong way.

Basically I have a query like this (and I had to change all the table/column names to protect IP so if there's a syntax error somewhere don't worry about it)

declare @sec_class_ids table (CLASS_ID varchar(50)); 

insert @sec_class_ids (CLASS_ID) values 
('987987987'), -- END USER - SAVE AND EXPORT [987987987]
('654654654'), -- END USER - SAVE [654654654]
('321321321') -- 'END USER - SPECIAL - SAVE AND EXPORT [321321321]'


select * from (
    select
        class.NAME as sec_class_name,
        sec_attr.NAME as sec_attr,
        'YES' as granted
    from sec_class class
    inner join class_sec_attr 
        on class.class_id = class_sec_attr.class_id
    inner join sec_attr
        on sec_attr.sec_attr_id = class_sec_attr.sec_attr_id
    inner join @sec_class_ids input
        on input.class_id = class.class_id
    ) as sec_attrs
pivot (
    max(sec_attrs.granted)
    --for sec_attrs.sec_class_id in (@sec_class_ids)
    for sec_points.sec_class_name in ([END USER - SAVE AND EXPORT],[END USER - SAVE],[END USER - SPECIAL - SAVE AND EXPORT])
) as sec_class_comparison
;

I would like to be able to use the table var (shown in the comment) rather than manually setting the columns for each query. I am aware this is possible and quite easy with dynamic SQL, but I'd like to avoid doing that if possible in any way.

Answer 1

Unfortunately there is no way to do this without dynamic SQL. PIVOT requires that the values are known when the query is executed so if you have unknown names, then you have to use dynamic SQL.