pivot - SQL Command - for multiple columns -

pivot - SQL Command - for multiple columns -

i'm having table columns - invdate, custname, invamt, paytype, , paidamt. amount can paid client either cash, cheque or credit card (paytype). based on table, want this:

select invdate, custname, invamt, (paidamt cashpay paytype = 'cash'), (paidamt cheqpay paytype = 'cheque'), (paidamt ccpay paytype = "cc') invoice

you did not specify rdbms using should able pivot info using aggregate function case look in database:

select invdate, custname, invamt, sum(case when paytype = 'cash' paidamt else 0 end) cashpay, sum(case when paytype = 'cheque' paidamt else 0 end) cheqpay, sum(case when paytype = 'cc' paidamt else 0 end) ccpay invoice grouping invdate, custname, invamt

if using database pivot function (sql server 2005+/oracle 11g+), can use:

select * ( select invdate, custname, invamt, paytype, paidamt invoice ) src pivot ( sum(paidamt) paytype in (cash, cheque, cc) ) piv

there ways can done joining on table multiple times need provide additional details table construction build query.

sql pivot