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.