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.

<span class="kwrd">DECLARE</span> @IDList <span class="kwrd">VARCHAR</span>(<span class="kwrd">MAX</span>)<br /><font color="#0000ff"></font><br /><span class="kwrd">SELECT</span> @IDList = STUFF((<span class="kwrd">SELECT</span> <span class="str">', ['</span> + AttributeID + <span class="str">']'</span> <span class="kwrd">AS</span> [text()]<br /><span class="kwrd">FROM</span> (<span class="kwrd">SELECT</span> <span class="kwrd">DISTINCT</span> <span class="kwrd">CONVERT</span>(<span class="kwrd">VARCHAR</span>, AttributeID) <span class="kwrd">as</span> AttributeID<br />        <span class="kwrd">FROM</span> Attribute) Y <br />        <span class="kwrd">ORDER</span> <span class="kwrd">BY</span> AttributeID<br />        <span class="kwrd">FOR</span> XML <span class="kwrd">PATH</span>(<span class="str">''</span>)), 1, 1, <span class="str">''</span>)

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.

Tweet about this on TwitterShare on Google+Share on FacebookShare on RedditEmail this to someoneShare on LinkedInShare on StumbleUpon

Leave a Reply

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