SQL Server pivoting data with dynamic sql

Example of how to dynamically generate sql statement to PIVOT data in SQL Server 2005 and above. You can put this code into stored procedure and use it instead of a view. I'm not aware of any alternative to that approach as you can't call stored procedure from the view and you can't dynamically define return table columns for table-valued functions.

CREATE TABLE #Attributes (
id INT,
name VARCHAR(50),
value VARCHAR(50),
dt DATETIME);
INSERT INTO #Attributes VALUES (1,'Field1', 'A', '20080701');
INSERT INTO #Attributes VALUES (2,'Field1', 'B', '20080701');
INSERT INTO #Attributes VALUES (3,'Field1', 'C', '20080701');
INSERT INTO #Attributes VALUES (3,'Field2', 'X', '20080701');
INSERT INTO #Attributes VALUES (4,'Field1', 'D', '20080702');
INSERT INTO #Attributes VALUES (4,'Field2', 'X', '20080702');
INSERT INTO #Attributes VALUES (5,'Field1', 'E', '20080702');
INSERT INTO #Attributes VALUES (5,'Field2', 'X', '20080702');
SELECT * FROM #Attributes
DECLARE @cols NVARCHAR(2000)
DECLARE @colsmax NVARCHAR(2000)
SELECT 
  @cols = COALESCE(@cols + ',[' + Name + ']','[' + Name + ']'),
  @colsmax = COALESCE(@colsmax + ',MAX([' + Name + ']) ['+ Name +']','MAX([' + Name + ']) ['+ Name +']')
FROM    #Attributes
GROUP BY Name
ORDER BY Name
SELECT @cols
SELECT @colsmax
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT b.*
FROM
(SELECT id, dt, '+
@colsmax +'
   FROM (
  SELECT id, dt, name, value
  FROM #Attributes 
   ) AS a
   PIVOT ( 
   max(Value) FOR Name IN ( '+ @cols +' )
) AS PivotTable
   GROUP BY id, dt) b'
SELECT @query
EXECUTE(@query)
DROP TABLE #Attributes