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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

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

marked as duplicate by Paul White Sep 27 '15 at 1:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

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