對同一欄位的多個值進行聚合查詢

我正在使用 SQL-Server 2012，我有一張信用卡交易表，如下所示：

CardScheme  RespCode
=========================
Visa        Declined
MasterCard  Approved
MasterCard  Approved
MasterCard  Declined
Visa        Declined
MasterCard  Approved
MasterCard  Declined
Visa        Approved
Visa        Approved
MasterCard  Approved

我需要編寫一個查詢以獲得以下結果

CardScheme  Declined  Approved
==============================
Visa        2         2 
MasterCard  2         4

請幫忙

由於您使用的是 SQL Server，因此您可以使用PIVOT函式將行轉換為列：

select cardScheme, Declined, Approved
from yt
pivot
(
 count(RespCode)
 for RespCode in (Declined, Approved)
) piv;

請參閱帶有展示的 SQL Fiddle

SELECT
   CardScheme,
   COUNT(CASE WHEN RespCode = 'Declined' THEN 1 END) AS Declined,
   COUNT(CASE WHEN RespCode = 'Approved' THEN 1 END) AS Approved
FROM
   MyTable
GROUP BY
   CardScheme;

引用自：https://dba.stackexchange.com/questions/43317