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
Best Answer
It's getting upset about your other columns. So try:
... 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.