7. January 2008 04:02
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.
DECLARE @IDList VARCHAR(MAX)
SELECT @IDList = STUFF((SELECT ', [' + AttributeID + ']' AS [text()]
FROM (SELECT DISTINCT CONVERT(VARCHAR, AttributeID) as AttributeID
FROM Attribute) Y
ORDER BY AttributeID
FOR XML PATH('')), 1, 1, '')
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.