Hi celticfire63, your method below gives same result as michael zrax.
Luis Cazares method gives what michael zrax wanted.
-----------------------------------------------
--DATA:
CREATE TABLE #tbldaysworked(
dayworkeddate,
namevarchar(10))
INSERT #tbldaysworked
SELECT '20140110', 'Harry' UNION ALL
SELECT '20130110', 'Dick' UNION ALL
SELECT '20130110', 'Tom'
SELECT * FROM #tbldaysworked
-----------------------------------------------
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SET @DynamicPivotQuery =
N'SELECT * FROM
(SELECT dayworked, name, fullday
FROM #tbldaysworked) AS DW --just to give this inner query an alias
PIVOT(max(fullday)
FOR name IN (' + @ColumnName + ')) AS PVTTable)'
SELECT @DynamicPivotQuery
DROP TABLE #tbldaysworked