如何根据列的唯一值对列进行分组并根据其值来计入不同的列？

Question

我有一个分类表如下，

我想检索结果如下根据scStatus值，并通过catID

catID | 0 | 2 | 3 
----- |---|---|--- 
2 | 1 | 0 | 1 
3 | 1 | 1 | 0 
4 | 2 | 0 | 1 
5 | 0 | 1 | 0 

其计数组我想这一点，select catID,count(scStatus) as [Count] from tableName group by catID,scStatus order by catID但我不能进入列的值。

`

Answer 1

使用枢轴查询：

SELECT catID, 
     SUM(CASE WHEN scStatus = 0 THEN 1 ELSE 0 END) AS [0], 
     SUM(CASE WHEN scStatus = 2 THEN 1 ELSE 0 END) AS [2], 
     SUM(CASE WHEN scStatus = 3 THEN 1 ELSE 0 END) AS [3] 
FROM Category 
GROUP BY catID 

Answer 2

枢操作

select  * 
from  (select catID,scStatusvalue from t) t 
       pivot (count(scStatusvalue) for scStatusvalue in ([0],[2],[3])) t