return single row using case in sqlServer [Resolved]

Posted by Rashedbin under Sql Server on 12/13/2015 | Points: 10 | Views : 1483 | Status : [Member] | Replies : 2
hi i keep my purchase and issue in my transaction table with type 0 and 1. now i want to retrieve sum of purchase and issue in single row with two column. my query is.........

select (case when TransType = 0 then SUM(Amount) end) PurchaseAmt, (case when TransType = 1 then SUM(Amount) end) IssueAmt from TransMaster where TransType in (0,1) group by TransType

abov query return following thow row.

[PurchaseAmt                   IssueAmt
13600123.00 NULL
NULL 12952262.00][1]

but my expectation is single row as following.

PurchaseAmt                   IssueAmt
13600123.00 12952262.00

is it possible ? thanks to all.




Responses

Posted by: Rajnilari2015 on: 12/13/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try



SELECT

MAX((case when TransType = 0 then SUM(Amount) end)) PurchaseAmt,
MAX((case when TransType = 1 then SUM(Amount) end)) IssueAmt
FROM ......


E.g. try this

;with cte as(

Select 13600123.00 as PurchaseAmt ,NULL as IssueAmt
Union All

Select Null ,12952262.00)

select MAX(PurchaseAmt) PurchaseAmt,MAX(IssueAmt) IssueAmt
from cte


Result
PurchaseAmt	IssueAmt

13600123.00 12952262.00


hope this helps

--
Thanks & Regards,
RNA Team

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

Posted by: PROFESSIONALUSER on: 12/14/2015 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
--sample data
declare @TransMaster table ( TransType int, Amount int)
insert @TransMaster values(1, 12234), (0, 1343), (1, 3243), (1, 3432), (0,4124)

--query
select SUM(case when TransType = 0 then Amount end) PurchaseAmt
, SUM(case when TransType = 1 then Amount end) IssueAmt
from @TransMaster
where TransType in (0,1)


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

Login to post response