SQL

How to Retrieve Data From More Than One Row into Columns

5
(1)

Suppose we have one Table named Account as below

Act_idAct_NameTr_TypeAmountBalance
1001XyzCredit2000020000
1001XyzDebit500015000
1002AbcCredit1500015000
1002AbcDebit300012000
1001XyzCredit500020000
1002AbcCredit700019000

Then i want to retrieve like following table

Act_idTotal_CreditTotal_DebitBalance
100125000500020000
100222000300019000

 

To get the like above table follow the query….

SELECT Act_id,Sum(Case when [Tr_Type]='Credit' then Amount end) as 'Total_Credit',
     sum(case when [Tr_Type]='Debit' then Amount end) as 'Total_Debit',
     (Sum(Case when [Tr_Type]='Credit' then Amount end)-Sum(Case when [Tr_Type]='Debit' then Amount end)) as Balance
     FROM [TestExam].[dbo].[Account] GROUP BY Act_id

Thank You.

How useful was this post?

Click on a star to rate it!

Leave a Reply

Your email address will not be published. Required fields are marked *