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.

Did you try a SELECT Subquery ( in (SELECT DesiredColumn FROM @TableName) ) in the commented line, instead of just in (@TableName)? — Tab Alleman
– Tab Alleman, Commented Jun 30, 2016 at 19:15
There's nothing wrong with dynamic SQL if you're properly writing it and implementing it. There's no actual security risk unless it's written incorrectly. — ZLK
– ZLK, Commented Jul 1, 2016 at 3:26

Taryn · Accepted Answer · 2016-06-30 18:20:28Z

4

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.

answered Jun 30, 2016 at 18:20

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Over a year ago

Thank you for the answer. I always rail against using dynamic sql because it can be an inherent security risk. I suppose what I'll do is create another table var or temp table containing a list of all known security attributes, another one for security classes, IDs, etc. and validate input against that list (whitelist the parameters).

Joe C · Accepted Answer · 2016-06-30 18:29:40Z

0

I have never heard of a way to do this with a query, however I remember I was able to set this up in a report (SSRS) which was really neat. Depending on what you plan to do with the data this might work for you.

answered Jun 30, 2016 at 18:29

Joe C

3,9832 gold badges14 silver badges33 bronze badges

1 Comment

Stefan Steiger Over a year ago

That's with a matrix control (aka PIVOT TABLE).

Collectives™ on Stack Overflow

Dynamic pivot columns without dynamic SQL statement?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related