CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Tom
Tom

Reputation: 1373

SQL Show 2 rows in one row

I have a query like this.

Query

After executing I'm getting following result.

Result

What I want to do is for each Invoice No I want to show 'Amount' as a CashAmt and ChequeAmt in a one row. Currently there is 2 records for cash and cheque.

Upvotes: 0

Views: 77

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You're trying to pivot your results. You can do this with group by, max and case:

select mdr.invoiceno, mdr.invoicedate, mdr.customerid, mdr.netamount, cus.name,
    max(case when pt.Name = 'Cash' then pay.amount end) CashAmt,
    max(case when pt.Name = 'Cheque' then pay.amount end) ChequeAmt
from customer cus
    ....
group by mdr.invoiceno, mdr.invoicedate, mdr.customerid, mdr.netamount, cus.name
order by mdr.invoiceno

Upvotes: 3

Related Questions