Selecting a set of distinct values and counting them individually into new columns

Question

I'm fairly sure this is an easy thing to do, but I'm a newbie at SQL so be gentle. If I want to write a query, that adds up the total occurences of each process number and stores those values to a new column, what do I do? I thought some mixture of count(distinct ...) could get it down but I'm not sure. See the result table for what I'm looking for.

Order_Table:

order_number     process 
         100           8
         100           7
         100           7
         100           6
         100           5
         105           6
         105           2
         105           4

Results:

order_num    NumOfEight   NumOfSeven   NumOfSix   NumOfFive  NumOfFour  NumOfTwo
      100             1            2          1           1          0         0
      105             0            0          1           0          1         1

Update: I'm using SQL 2005 as a base, but have access to newer versions. Process is a finite set of values.

Answer 1

select order_num,
       sum(case when process = 8 then 1 else 0 end) as NumOfEight,
       sum(case when process = 7 then 1 else 0 end) as NumOfSeven,
       sum(case when process = 6 then 1 else 0 end) as NumOfSix
       /* Repeat as many times as needed */
    from Order_Table
    group by order_num

Answer 2

Assuming SQL Server 2005+ you can use PIVOT

SELECT 
      order_num, [8] AS NumOfEight, [7] AS NumOfSeven   /* etc. etc.*/
FROM 
      (SELECT order_number AS order_num, process FROM Order_Table) p
PIVOT 
      (COUNT(process) FOR process IN ([8],[7] /* etc. etc.*/)) pvt