Pivots giving too many results I'm trying to learn how this pivot thing works. I don't think I'm understanding it quite right.
I just want to do a simple count for a bunch of item types that have some statuses. The table I'm working from has columns: [Status], [ItemType], and a bunch of others.

Here is what I'm doing currently 

SELECT [NEW]
    ,[IN]
    ,[OUT]
    ,READY = [READY FOR DISPOSAL] 
    ,TRANSIT = [IN ROUTE TO DISPOSAL]
    ,[DISPOSED]
    ,[TYPE] = [ItemType] 
FROM [EVAULTTEST].[dbo].[Items] AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
    , [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv


And the result

1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
1   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   1   0   0   0   AUTOMOBILES
0   0   1   0   0   0   AUTOMOBILES


Why don't these collapse into something like this:

2   0   0   0   0   0   FIREARMS
0   2   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
3   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   2   0   0   0   AUTOMOBILES
It's getting upset about your other columns. So try:

SELECT [NEW]
,[IN]
,[OUT]
,READY = [READY FOR DISPOSAL] 
,TRANSIT = [IN ROUTE TO DISPOSAL]
,[DISPOSED]
,[TYPE] = [ItemType] 
FROM (SELECT ItemType, Status FROM [EVAULTTEST].[dbo].[Items]) AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
, [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv


... and this is because the implicit grouping is on any column that's not in the PIVOT clause. So removing the extra columns in the first part of the query should fix it.