CREATE TABLE #X1
(vcEmployeeUniqueID VARCHAR(20),
vcLastName VARCHAR(20),
vcFirstName VARCHAR(20),
vcSchoolName VARCHAR(50),
iSchoolYearCode INT)


INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2009)
INSERT INTO #X1 VALUES ('001210214','Miller','Helen','STAMFORD HIGH SCHOOL',2010)
INSERT INTO #X1 VALUES ('001210214','Miller','Helen','NORWALK HIGH SCHOOL',2011)
INSERT INTO #X1 VALUES ('003215847','Carolyn','June','NORWALK HIGH SCHOOL',2010)
INSERT INTO #X1 VALUES ('003215847','Carolyn','June','WATERBURY HIGH SCHOOL',2011)
INSERT INTO #X1 VALUES ('002149013','Steve','Ulman','STAMFORD HIGH SCHOOL',2011)

vcEmployeeUniqueID	vcLastName	vcFirstName	SY2009			SY2010			SY2011	
001210214		Miller		Helen		NORWALK HIGH SCHOOL	STAMFORD HIGH SCHOOL	NORWALK HIGH SCHOOL	
003215847		Carolyn		June					NORWALK HIGH SCHOOL	WATERBURY HIGH SCHOOL
002149013		Steve		Ulman								STAMFORD HIGH SCHOOL

Select vcEmployeeUniqueID, [2009],[2010],[2011]
 from
( Select vcEmployeeUniqueID, 
		 vcLastName, 
		 vcFirstName,
		 vcSchoolName,
		 iSchoolYearCode from #X1) as sourcetable
Pivot (  count(vcSchoolName) for iSchoolYearCode in ([2009],[2010],[2011])) as pivotable 

vcEmployeeUniqueID	2009	2010	2011
001210214		1	1	1
002149013		0	0	1
003215847		0	1	1

SELECT   vcEmployeeUniqueID
        ,vcLastName
        ,vcFirstName
        ,[2009]                 AS SY2009
        ,[2010]                 AS SY2010
        ,[2011]                 AS SY2011
FROM    #X1 AS src
PIVOT  (MAX(vcSchoolName) 
        FOR iSchoolYearCode IN ([2009], [2010], [2011])) AS pvt
ORDER BY vcEmployeeUniqueID