TSQL - Looking for code clarification

问题:

This is the code I used and the result is posted below:

SELECT * 
FROM
    (SELECT 
         P.ProductID, PC.Name, ISNULL (P.Color, 'uncolored') AS color
     FROM 
         SalesLT.ProductCategory AS PC
     JOIN 
         SalesLT.Product AS P ON PC.ProductCategoryID = P.ProductCategoryID
) AS PPC
PIVOT 
    (COUNT (ProductID) FOR Color IN ([Red],[Blue],[Silver],[Black],[Yellow],[Grey],[Multi],[Uncolored])) AS pvt
ORDER BY 
    Name;

I didn't request the query to provide me the name, could someone explain me how this 'name' thing popped up in the result? How can I modify the code if I want ProductID to appear instead of name?

Any help or feedback would be greatly appreciated.

回答1:

ProductID won't show because you have used it as an aggregation column while pivoting the table. Only the counts of product Id for each colour will be shown. For seeing ProductID remove the pivot part of query. Name shows up in your final result because it is there in your inner query.

Pivoting the table changes rows to columns. In your case the row values for colours Red,Blue,Silver,Black,Yellow,Grey,Multi,Uncolored ( as given inside pivot clause) are changed to columns. Under each of these columns, the counts of ProductIDs present in the table for that colour and the name in 'Name' column are shown. So nowhere in the query individual productIds are shown. The ProductID column the inner query is only used for calculating counts and not shown in final result.

回答2:

PIVOT is just a operator defined by SQL Server, even it's not standard SQL operator. How would you pivot data without PIVOT operator? It's easy. For simplicity, let's ignore null color.

SELECT 
     P.ProductID, PC.Name, 
     COUNT(CASE WHEN P.Color = 'Red' THEN 1 END) AS Red,
     COUNT(CASE WHEN P.Color = 'Blue' THEN 1 END) AS Blue,
     ...
FROM 
     SalesLT.ProductCategory AS PC
JOIN 
     SalesLT.Product AS P ON PC.ProductCategoryID = P.ProductCategoryID
GROUP BY P.ProductID, PC.Name;

PIVOT does exact the same operation as above query. You noticed that you don't need write GROUP BY clause with PIVOT operator. That's because

(From Itzik Ben-Gan's T-SQL Fundamentals)

The PIVOT operator figures out the grouping elements implicitly by elimination. The grouping elements are all attributes from the source table that were not specified as either the spreading element or the aggregation element.

To solve your problem, just don't put ProductId attribute to PIVOT operator's aggregation element. COUNT any attribute will work in your case. You can write

PIVOT COUNT(Color) FOR Color IN ...