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.
|
1 |
<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.