Output a 2 column table to a three column query Hi I have a Table with 2 columns, one column for the employeename and another column for shift,  below is a sample.

EmployeeName          Shift
John                    1
Peter                   1
Mary                    2
Mike                    1
John                    2
Paul                    2
Mary                    1


I want to output it to query with 3 columns and count the number of shifts as show below:

EmployeeName         Shift1            Shift2
John                   1                 1
Peter                  1                 0
Mary                   1                 1
Mike                   1                 0
Paul                   0                 1
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 #