I have a table like below ...
Serv nm Mnth sc se
F 01 Jan 100 5
M 01 Jan 200 2
A 01 Jan 100 7
F 02 Feb 200 3
A 02 Feb 100 3
how could I transform it like ....
nm Mnth SumF(sc) SumF(se) SumM(sc) SumF(se) SumA(sc) SumA(se)
01 Jan 100 5 200 2 100 7
02 Jan 200 3 NULL NULL 100 3
Assuming you need to go DYNAMIC
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName('Sum'+Serv+'(sc)')+','+QuoteName('Sum'+Serv+'(se)') From Yourtable Order by 1 For XML Path('')),1,1,'')
Select @SQL = '
Select [nm],[Mnth],' + @SQL + '
From (
Select [nm]
,[Mnth]
,C.*
From YourTable A
Cross Apply (Values (''Sum''+A.Serv+''(sc)'',A.sc)
,(''Sum''+A.Serv+''(se)'',A.se)
) C (Item,Value)
) A
Pivot (sum(Value) For [Item] in (' + @SQL + ') ) p'
Exec(@SQL);
Returns
The Generated SQL (If you DON'T need Dynamic)
Select [nm],[Mnth],[SumA(sc)],[SumA(se)],[SumF(sc)],[SumF(se)],[SumM(sc)],[SumM(se)]
From (
Select [nm]
,[Mnth]
,C.*
From YourTable A
Cross Apply (Values ('Sum'+A.Serv+'(sc)',A.sc)
,('Sum'+A.Serv+'(se)',A.se)
) C (Item,Value)
) A
Pivot (sum(Value) For [Item] in ([SumA(sc)],[SumA(se)],[SumF(sc)],[SumF(se)],[SumM(sc)],[SumM(se)]) ) p
You can first unpivot the value to get both the se and sc in same column and then do a pivot like this:
select *
from (
select
serv+'_'+t serv, nm, mnth, s
from t
cross apply (
values
('sc', sc),
('se', se)
) x (t, s)
) t pivot (
sum(s) for serv in (
[F_sc],[F_se],
[M_sc],[M_se],
[A_sc],[A_se]
)
) as p;
Produces:
