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 
  • (What do you want to happen if there are two rows with John, 1?) – Aaron Bertrand Jun 7 '14 at 15:10
  • John i have a table similar to these and need to count two conditions, so queries have different where clause need to combine them in 1 result. – Arvin Jun 7 '14 at 15:25

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 #

  • Actually 1 and 2 works well, the pivot i can not get to work properly.. a million thanks, im trying to figure this one out for the past 2 days, btw i am new in sql – Arvin Jun 7 '14 at 15:49
select EmployeeName, max(shift1) as shift1, max(shift2) as shift2
from (
    select EmployeeName
         , case when shift = 1 then 1 else 0 end as shift1
         , case when shift = 2 then 1 else 0 end as shift2
    from T
) as t2
group by EmployeeName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.