This returns exactly what you need.

This script uses table Tally which contains sequential integer numbers.

You may create it yourself or search this forum for one of scripts creating it.

[Code]

Create Table tTestData (

StaffName varchar(40),

ReviewDate smalldatetime

)

SET DATEFORMAT YMD

Insert Into tTestData(StaffName, ReviewDate)

Select 'Blow, Joe','2008-01-24' Union All

Select 'Doe, Jane','2008-01-22' Union All

Select 'Dunno, I', '2007-07-05' Union All

Select 'Blow, Joe','2007-06-28' Union All

Select 'Doe, Jane','2007-06-11' Union All

Select 'Blow, Joe','2007-03-13' Union All

Select 'Blow, Joe','2007-12-21' Union All

Select 'Doe, Jane','2007-12-16' Union All

Select 'Doe, Jane','2007-03-12' Union All

Select 'Dunno, I', '2007-12-18' Union All

Select 'Blow, Joe','2006-03-23' Union All

Select 'Blow, Joe','2006-07-18' Union All

Select 'Doe, Jane','2006-05-16' Union All

Select 'Doe, Jane','2006-11-19' Union All

Select 'Dunno, I', '2006-12-10'

GO

CREATE FUNCTION dbo.tReviewsOfYear

(

@ReviewYear SMALLDATETIME

)

RETURNS @reviews TABLE (

ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

StaffName VARCHAR(40),

ReviewNo int NULL,

ReviewDate SMALLDATETIME NOT NULL

)

AS

BEGIN

--To make sure we deal with "year only" value

SET @ReviewYear = DATEADD(YY, DATEDIFF(YY, 0, @ReviewYear), 0)

DECLARE @StaffName VARCHAR(200)

DECLARE @Count INT

INSERT INTO @reviews (StaffName, ReviewDate)

SELECT StaffName, ReviewDate

FROM dbo.tTestData

WHERE ReviewDate >= @ReviewYear

ANDReviewDate < DATEADD(YY, 1, @ReviewYear)

ORDER BY StaffName, ReviewDate

-- Sequential update driven by PRIMARY KEY

UPDATE R

SET @Count = ReviewNo = CASE WHEN @StaffName = StaffName THEN @Count + 1 ELSE 1 END,

@StaffName = StaffName

FROM @reviews R

RETURN

END

GO

select CASE WHEN T.N = 1 THEN S.StaffName ELSE '' END,

T.N, Y2006.ReviewDate [2006_Dates], Y2007.ReviewDate [2007_Dates], Y2008.ReviewDate [2008_Dates]

from dbo.tTestData S

INNER JOIN dbo.Tally T ON T.N > 0 AND T.N < 5000

-- If you expect more than 5000 reviews per year per customer increase this number

LEFT HASH JOIN dbo.tReviewsOfYear ('2006-01-01') Y2006 ON S.StaffName = Y2006.StaffName AND T.N = Y2006.ReviewNo

LEFT HASH JOIN dbo.tReviewsOfYear ('2007-01-01') Y2007 ON S.StaffName = Y2007.StaffName AND T.N = Y2007.ReviewNo

LEFT HASH JOIN dbo.tReviewsOfYear ('2008-01-01') Y2008 ON S.StaffName = Y2008.StaffName AND T.N = Y2008.ReviewNo

WHERE Y2006.ReviewNo IS NOT NULL OR Y2007.ReviewNo IS NOT NULL OR Y2008.ReviewNo IS NOT NULL

GROUP BY S.StaffName, T.N, Y2006.ReviewDate , Y2007.ReviewDate , Y2008.ReviewDate

ORDER BY S.StaffName, T.N

[/Code]