SQLTeam.com | Weblogs | Forums

How to combine multiple named Columns as a Row and sum of it

sql2008
sql2012

#1

I need a query in SQL to combine several rows in the same table.

L_Life_Amount  M_LifeAmount    L_Health_Amount    M_Health_amount
100                 200             300                 400
200                 300             400                 600
300                 400             500                 700

and I need to bring the below result set

InsuranceType  L_Amount     M_Amount    L_amount-M_amount 
Life amount    600          900         -300
Health amount   1200        1700        -500
Total           1800        2600        -800

Here I need to sum the values of L_life_Amount(L_Amount) and sum of M_Life_amount(M_Amount) and display it as Life amount

the same way I need to do sum for L_Helath_Amount(L_Amount) and sum of M_Health_Amount(M_Amount) and display it as Health Amount

Finally I make the diff betweeen L_amount and M-amount and make a total of it.


#2

select
'Life amount' as InsuranceType, sum(L_Life_Amount) L_Amount, sum(M_LifeAmount) M_Amount, sum(L_Life_Amount)-sum(M_LifeAmount) as 'L_amount-M_amount'
from table1
union
select
'Health amount', sum(L_Health_Amount), sum(M_Health_amount), sum(L_Health_Amount) - sum(M_Health_amount)
from table1
union
select
'Total', sum(L_Health_Amount), sum(M_Health_amount), sum(L_Health_Amount) - sum(M_Health_amount)
from table1