SELECT e.firstName,c.CompanyName FROM Employees e JOIN Orders o ON e.employeeID=o.employeeID JOIN Customers c ON c.customerID=o.customerID ORDER BY e.firstName,c.CompanyName | |
Compelling, but we see that there's many repeated entries in the list. We'd like to count up how many sales are at the intersection of each distinct customer and salesperson. To do this we'll add in a COUNT function and GROUP BY clause. SELECT e.firstName,c.CompanyName, COUNT(o.orderID) FROM Employees e JOIN Orders o ON e.employeeID=o.employeeID JOIN Customers c ON c.customerID=o.customerID GROUP BY e.firstName,c.CompanyName ORDER BY e.firstName,c.CompanyName | |
Now the real fun begins as we transition this into a query that uses PIVOT. Because we're grouping by not one but two columns, this query is a good candidate for PIVOT. We'll start simple with a query that doesn't JOIN anything, but just references the Orders table. We'll choose to have Employee data put into columns since there are only 9 employees. Much more manageable. In this case the numbers 1 through 9 refer to the ID of employees:
SELECT * FROM Orders PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

In the result set notice that the specific columns returned come from the result of the PIVOT clause and not directly from the Orders table! And that PIVOT clause brings up every column from the Orders table except for employeeID, which gets weeded out since it is used in the aggregate function COUNT. If it helps to visualize what's going on here, it's as if we had said “GROUP BY *” if such a thing existed. Another interesting thing about PIVOT is that there can be only one aggregate function and column referenced. In this case it's COUNT(employeeID).
To limit the columns returned to just what we need to show and aggregate, we'll have to use a