SQL help pivoting a single table

Question

I have a table containing several columns. There are multiple records that contain the same info except for the last column which is different. I need this last column to be combined into one row as additional columns. There will only be up to 3 additional columns (course, course2, course3). Below is my table layout. Thanks for any help.

Columns
=============
EmployeeNumber  
Email   
LastName    
FirstName   
Address1    
City    
State   
Zip 
Phone   
Certified   
School  
EmployeeType    
BirthDate   
Course  
StaffNumber

Let me try a smaller example than my table...

StaffID  FName       LName      Course
=========================================
1111      John       Smith      History
1111      John       Smith      AP History
1111      John       Smith      Economics
2222     Jane       Smith       Science
2222     Jane       Smith       Chemistry
2222     Jane       Smith       Geology

I need it to read...

StaffID  FN       LN      Course1  Course2     Course3
=======================================================
1111    John     Smith    History  AP History  Economics
2222   Jane      Smith     Science   Chemistry   Geology

The only column that will contain different data that I need to combine is Course. Otherwise, there will be several rows with identical data that I need to combine into one.

Answer 1

SELECT *
FROM
(
  SELECT
  EmployeeNumber,
  Email, LastName, FirstName,
  Address1, City, State,
  Zip, Phone,
  Certified,
  School,
  EmployeeType,
  BirthDate,
  StaffNumber,
  Course as CourseName,
  CONCAT('Course', row_number() over (partition by  EmployeeNumber order by Course)) AS Col
  FROM employee_courses
) src
PIVOT
(
   MAX(CourseName) 
   FOR Col IN ([Course1],[Course2],[Course3])
) pvt