0

I am using SQL-Server 2012, I have a table of credit card transactions as follows:

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

I need to write a query to get the following results

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

Please help

5

Since you are using SQL Server you can use the PIVOT function to convert rows into columns:

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

See SQL Fiddle with Demo

3
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;

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