SQLTeam.com | Weblogs | Forums

Return 2 values from single column as separate columns

As an example, say my table has columns for customer, payment type and sale amount and I’m only interested in payment types of cash or credit. I'm using sum in the select statement to get total sales and the where clause is limited to cash/credit payments so my output has three columns with two rows for each customer as warranted. How would I adjust the query so I still end up with three columns but have one row for each customer with the total sales for cash and credit payments as the second and third columns respectively? Thanks

Much easier to help you with a sample table and data. Pleease provide via ddl and dml

So the query looks like this:

SELECT [CUSTOMER], [PAYMENT], SUM([AMOUNT]) AS TOTAL
FROM [TRANSACTIONS]

WHERE TRANS_DATE >= '2022-02-01' AND TRANS_DATE < '2022-03-01' AND PAYMENT IN ('CASH','CREDIT') 

GROUP BY CUSTOMER, PAYMENT

Sample Data
1

Current Output
2

Desired Output
3

Query:


SELECT [CUSTOMER], 
    SUM(CASE WHEN PAYMENT = 'CASH' THEN [AMOUNT] ELSE 0 END) AS CASH,
    SUM(CASE WHEN PAYMENT = 'CREDIT' THEN [AMOUNT] ELSE 0 END) AS CREDIT
FROM [TRANSACTIONS]
WHERE TRANS_DATE >= '2022-02-01' AND TRANS_DATE < '2022-03-01' AND 
    PAYMENT IN ('CASH','CREDIT') 
GROUP BY CUSTOMER
ORDER BY CUSTOMER