CODEWITHSUNDEEP
sql-server-2008rows
user1138780
user1138780

Reputation: 135

Getting row values to columns in SQL server select query

I am working on ans SQL server database, to bring the result in a specific format. I have the data in the table as 

Category      AmountType       EligibleAmount  NonEligibleAmount

 100              FA               1500.00       1200.00
 100              IA               2000.00       1300.00
 200              FA               1000.00        800.00
 200              IA               3500.00       2100.00

I want the output to be as

Category  FA EligibleAmt  FA NonEligibleAmt  IA EligibleAmt   IA NonEligibleAmt  

 100        1500.00          1200.00              2000.00        1300.00
 200        1000.00           800.00              3500.00        2100.00

There could be only two rows for a category in the table at any point of time.Could anyone help me in this please?

Upvotes: 0

Views: 1158

Answers (1)

Angelo
Angelo

Reputation: 335

I think this will help:

SELECT CATEGORY, MAX(DISTINCT CASE WHEN AMOUNTTYPE = 'FA' THEN ELIGIBLEAMOUNT END) AS [FA ELIGIBLE], 
                 MAX(DISTINCT CASE WHEN AMOUNTTYPE = 'FA' THEN NONELIGIBLEAMOUNT END) AS [FA NON ELIGIBLE], 
                 MAX(DISTINCT CASE WHEN AMOUNTTYPE = 'IA' THEN ELIGIBLEAMOUNT END) AS [IA ELIGIBLE], 
                 MAX(DISTINCT CASE WHEN AMOUNTTYPE = 'IA' THEN NONELIGIBLEAMOUNT END) AS [IA NON ELIGIBLE]
FROM  dbo.TESTTABLE
GROUP BY CATEGORY

Upvotes: 1

Related Questions