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
up vote 3 down vote accepted

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.