CODEWITHSUNDEEP
sqlpivot
user2070294
user2070294

Reputation: 1

SQL Command - for multiple columns

I'm having a table with columns - InvDate, CustName, InvAmt, PayType, and PaidAmt. The amount can be paid by the customer either by Cash, Cheque or Credit Card (PayType). Now based on this table, I want something like this:

SELECT InvDate, CustName, InvAmt, 
       (PaidAmt as CashPay Where PayType = 'Cash'), 
       (PaidAmt as CheqPay WHERE PayType = 'Cheque'), 
       (PaidAmt As CCPay WHERE PayType = "CC') FROM Invoice

Upvotes: 0

Views: 208

Answers (2)

Taryn
Taryn

Reputation: 247810

You did not specify what RDBMS you are using but you should be able to pivot the data using an aggregate function with a CASE expression in any database:

select invDate, custName, InvAmt,
  sum(case when PayType = 'Cash' then PaidAmt else 0 end) CashPay,
  sum(case when PayType = 'Cheque' then PaidAmt else 0 end) CheqPay,
  sum(case when PayType = 'CC' then PaidAmt else 0 end) CCPay
from Invoice
group by invDate, custName, InvAmt

If you are using a database with a PIVOT function (SQL Server 2005+/Oracle 11g+), then you can use:

select *
from
(
  select invDate, custName, InvAmt,
    PayType, PaidAmt
  from Invoice
) src
pivot
(
  sum(paidamt)
  for paytype in (Cash, Cheque, CC)
) piv

There are also ways that this can be done by joining on your table multiple times but you would need to provide additional details about the table structure to properly construct that query.

Upvotes: 3

user806549
user806549

Reputation:

If you are using SQL Server, you could write something like:

SELECT InvDate, CustName, InvAmt, 
       CASE WHEN PayType = 'Cash' THEN PaidAmt ELSE 0 END CashPay,
       CASE WHEN PayType = 'Cheque' THEN PaidAmt ELSE 0 END CheqPay,
       CASE WHEN PayType = 'CC' THEN PaidAmt ELSE 0 END CCPay
FROM Invoice

Upvotes: 0

Related Questions