Dynamically Generate SQL 2005 Pivot

7 Jan

I’m working with an EAV schema (Open schema) and wanted to have a full pivot of my data available in a view, even after new fields were added to the source.

I found a clever way to dynamically generate the ID list (and the field list as well) so that you can generate the SQL dynamically.

This is essentially an inline way to do a string concatenation aggregate using the XML functionality in SQL 2005.

Assuming you have a table ‘Attribute’ with a field ‘AttributeID’, this is an easy way to create a delimited list (with [], ala pivot style) to generate the SQL for the pivot.  Use the same idea for your field list.

For performance sake, I don’t generate the pivot statement live, it is instead updated from a stored procedure when appropriate through a dynamically generated alter view.  I won’t bore you with the dynamic sql portion, as I’m sure you already know how to do that.

Leave a Reply

Your email address will not be published. Required fields are marked *