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