Output a 2 column table to a three column query
In SQL Server 2012 you can use the new IIF() function:

SELECT EmployeeName, 
  Shift1 = COUNT(IIF(Shift=1,1,NULL)),
  Shift2 = COUNT(IIF(Shift=2,1,NULL))
FROM dbo.tablename
GROUP BY EmployeeName;


This is just shorthand for a CASE expression, so on older versions you could do it this way:

SELECT EmployeeName, 
  Shift1 = COUNT(CASE WHEN Shift = 1 THEN 1 END),
  Shift2 = COUNT(CASE WHEN Shift = 2 THEN 1 END)
FROM dbo.tablename
GROUP BY EmployeeName;


You can also use PIVOT:

SELECT EmployeeName, Shift1 = [1], Shift2 = [2] 
FROM dbo.TableName AS t
PIVOT (COUNT(Shift) FOR Shift IN ([1],[2])) AS p;


Now, none of these seems to produce the results in the same order you've defined in the question, and I don't know it's possible to produce that order reliably given the sample data you've shown.

# SQLfiddle demo #