Getting row values to columns in SQL server select query

Question

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?

If you don't care which row you get for each category, you could just use GROUP BY in your select. Check out the documentation for GROUP BY — SaganRitual
– SaganRitual, Commented Aug 7, 2013 at 3:43
I don't want to group the rows, then I have to sum the amount values which I don't want to do. I want to bring the second row amount values in column format. — user1138780
– user1138780, Commented Aug 7, 2013 at 3:55

makes · Accepted Answer · 2013-09-24 19:54:53Z

1

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

edited Sep 24, 2013 at 19:54

makes

6,5683 gold badges42 silver badges59 bronze badges

answered Aug 8, 2013 at 5:46

Angelo

3354 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting row values to columns in SQL server select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related