How do I pivot this SQL result?

Whats the best way to PIVOT this SQL result? I was wondering if the count(*) can be done as part of the pivot instead of having to group the data prior?

SELECT 
    e.fullname,
    e.BusinessUnit,  
    COUNT(*) as total  
FROM EmpComplaints e  
WHERE e.BusinessUnit in ('1-Sales', '2-Tech', '3-Marketing')    
GROUP BY e.fullname, e.BusinessUnit
order by e.fullname, e.BusinessUnit

I am basically reporting on each employee the amount of reports they have in each of the three business units: sales, tech, marketing. and looking to get a result that will list开发者_如何学Go fullnames on the left with each name appearing once and each name having a column for ('1-Sales', '2-Tech', '3-Marketing') with a number in it that would be the count(*)

---

Is this MS SQL Server? This might work, sorry, don't have it running, so can't verify:

select fullname, 
    sum(case BusinessUnit when '1-Sales' then 1 else 0 end) as Sales,
    sum(case BusinessUnit when '2-Tech' then 1 else 0 end) as Tech,
    sum(case BusinessUnit when '3-Marketing' then 1 else 0 end) as Marketing
FROM EmpComplaints
GROUP BY fullname;

---

Here is how to do it in SQL Server 2005/2008:

SELECT
    FullName
    ,[1-Sales]
    ,[2-Tech]
    ,[3-Marketing]
     FROM
(
SELECT 
    e.fullname,
    e.BusinessUnit,
    COUNT(*) AS Total  
FROM EmpComplaints e  
WHERE e.BusinessUnit in ('1-Sales', '2-Tech', '3-Marketing')
GROUP BY e.fullname,e.BusinessUnit    
) AS bu
PIVOT
(
SUM(Total)
FOR BusinessUnit IN ([1-Sales], [2-Tech], [3-Marketing])
) AS pvt
ORDER by fullname