One table scan per each DateID? That seems quite expensive.

Here's my dynamic approach based on my initial query and your sample data. 😉

SET @SQL = ''

SELECT @SQL = @SQL + ',MAX( CASE WHEN DateID = ' + CAST( DateID AS varchar(10)) + ' THEN Value END) AS Date' + CAST( DateID AS varchar(10)) + CHAR(10)

FROM (SELECT DISTINCT DateID FROM #t1) t1

SET @SQL = 'SELECT Name ' + CHAR(10) + @SQL + CHAR(10) +

'FROM #t1

CROSS APPLY (VALUES

(1,''DateID'', CAST( DateID AS varchar(10))),

(2,''Height'', CAST( Height AS varchar(10))),

(3,''Weight'', CAST( Weight AS varchar(10))),

(4,''Attitude'', Attitude),

(5,''Build'', Build))x(RowOrder, Name, Value)

GROUP BY Name,RowOrder

ORDER BY RowOrder'

EXEC(@SQL)

No loops and the table is read once for the list of DateIds and once for the actual query.

You can find all details for this method on the link provided in my previous post. 😉