How to Retrieve Data From More Than One Row into columns?


Retrieve Data From More Than One Row into Columns

How to Retrieve Data From More Than One Row and show in a single row?



Suppose we have one Table named Account as below :

Table : Account


Act_id Act_Name Tr_Type Amount Balance
1001 Xyz Credit 20000 20000
1001 Xyz Debit 5000 15000
1002 Abc Credit 15000 15000
1002 Abc Debit 3000 12000
1001 Xyz Credit 5000 20000
1002 Abc Credit 7000 19000

Now i want to fetch all the Credit and Debit amount of a particular customer in two column not in row.
for example:

Expected Output :


Act_id Total_Credit Total_Debit Balance
1001 25000 5000 20000
1002 22000 3000 19000

Then you can use the following code to get the above result.

Code:



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



You can arrange the above code like your requirement.

Thank You.

Reference: http://msahoo.wordpress.com/2010/02/18/how-to-retrieve-data-from-more-than-one-row/


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: