How to Perform This sql Query - Bandi

Is this you are looking for ?

SELECT Number, CASE WHEN Payment>0 THEN Ledgertype+' Payment' ELSE LedgerType+ ' Receipt' END as Mode, 	Payment,	Receipt FROM TableName

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

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