Try the below options
--Create a table variable
DECLARE @T TABLE(Number INT,LedgerType VARCHAR(20),Payment INT,Receipt INT)
--Populate the table variable with some data
INSERT INTO @T VALUES
(1001,'Cash',1000,0),
(1002,'Adjust',0,1000),
(1003,'Cash',0,1500),
(1004,'Adjust',0,7500)
--Display the original record
SELECT *
FROM @t
Solution 1: (Using Case Statement) SELECT
t.Number,
CASE WHEN t.PAYMENT > 0 THEN t.LedgerType + ' Payment'
ELSE t.LedgerType + ' Receipt'
END Mode,
t.Payment,
t.Receipt
FROM @t t
Solution 2: (Using IIF Statement - Sql Server 2012 onwards) SELECT
t.Number,
IIF ( t.PAYMENT > 0 , t.LedgerType + ' Payment', t.LedgerType + ' Receipt' ) Mode,
t.Payment,
t.Receipt
FROM @t t
Solution 3: (Using CHOOSE Statement - Sql Server 2012 onwards) SELECT
t.Number,
CHOOSE( CASE WHEN t.PAYMENT > 0 THEN 1 ELSE 2 END, t.LedgerType + ' Payment', t.LedgerType + ' Receipt') Mode,
t.Payment,
t.Receipt
FROM @t t
In all the 3 ways, the output generated is as under
Number Mode Payment Receipt
1001 Cash Payment 1000 0
1002 Adjust Receipt 0 1000
1003 Cash Receipt 0 1500
1004 Adjust Receipt 0 7500
Hope this helps
--
Thanks & Regards,
RNA Team
Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator