Click here to Skip to main content
14,573,754 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,

My current report results are like this as an example:
Payment mode

Bill No.| Cash  | Credit Card| Google Pay| Phone Pe| TOTAL AMT|

000001    1000.0         0.0          0.0       0.0      1000.0
000002       0.0         0.0          0.0     956.0       950.0
000003       0.0       575.0          0.0       0.0       575.0
000004     824.0         0.0          0.0       0.0       824.0
000005       0.0         0.0        455.0       0.0       455.0

But I need one column named Payment Mode and the data to be captured like this
Bill No.| Payment Mode  | TOTAL

000001     Cash            1000.0
000002     Phone Pe         956.0
000003     Credit Card      575.0
000004     Cash             824.0
000005     Google Pay       455.0

Thanks in advance for the help!

What I have tried:

Need help, We can retain all the columns from table 1 and add another column named Payment Mode as in table 2 above.
Posted
Updated 14-Nov-19 18:29pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

What you seek is a CASE statement, which allows you to evaluate values and return one value. This is the SQL analog of a Switch() case block.

This is similar to using a IF...THEN...ELSE block of code.

I prefer to use the CASE block when I can as I feel it is cleaner looking, but I do have some really ugly queries that actually have an IF block within the ELSE clause

Here is a proof of concept; I kept it as all INT types for ease of typing/reading. Please not the ELSE clause.... if someone did a split-payment it would show up here as neither value would be equal to the Total
DECLARE @Invoices TABLE (BillNo INT, Cash INT, CreditCard INT, GooglePay INT, PhonePe INT, Total INT )

INSERT @Invoices VALUES
(000001, 1000.0, 0.0, 0.0, 0.0, 1000.0)
, (000002, 0.0, 0.0, 0.0, 956.0, 950.0)
, (000003, 0.0, 575.0, 0.0, 0.0, 575.0)
, (000004, 824.0, 0.0, 0.0, 0.0, 824.0)
, (000005, 0.0, 0.0, 455.0, 0.0, 455.0)

SELECT BillNo
,      CASE Total
            WHEN Cash       THEN 'Cash'
            WHEN CreditCard THEN 'CreditCard'
            WHEN GooglePay  THEN 'GooglePay'
            WHEN PhonePe    THEN 'PhonePe'
            ELSE            'Other'
      END as [PaymentMethod]
,     Total
FROM  @Invoices
Reference:
CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100