Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

2 Answers

up vote 5 down vote

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

share|improve this answer
up vote 3 down vote 
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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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