Convert column to row and sum query in different table

i trying to figure to convert sumtchtraffic and totalpayloadGb column in sitetable and sum data totalpayloadGb & sumtchtraffic where BTS_TYPE 2g, 3g and 4g in techtable

i tried like this, but i cant show totalpayloadgb & sumtchtraffic in column [SUM OF] ([sum of] is not in any table in my database)

select * from (select
sum(case when techtable.BTS_TYPE='2G' then sitetable.TotalPayloadGb else 0 end) as [Total 2G],
sum(case when techtable.BTS_TYPE='3G' then sitetable.TotalPayloadGb else 0 end) as [Total 3G],
sum (case when techtable.BTS_TYPE='4G'then sitetable.TotalPayloadGb else 0 end) as [Total 4G]

from sitetable 

inner join techtable on sitetable.sitename = techtable.sitename) as t

I want show my data like this:

Answer

One way would be to total your results separately, using the same conditional aggregation you have started, and then UNION the results together. Since your first column doesn’t exist anywhere else, you’ll have to hard-code it into the queries.

SELECT 
    'TotalPayloadGb' AS SumOf,
    sum(CASE WHEN t.BTS_TYPE = '2G' THEN s.TotalPayloadGb ELSE 0 END) AS [Total 2G],
    sum(CASE WHEN t.BTS_TYPE = '3G' THEN s.TotalPayloadGb ELSE 0 END) AS [Total 3G],
    sum(CASE WHEN t.BTS_TYPE = '4G' THEN s.TotalPayloadGb ELSE 0 END) AS [Total 4G]
FROM sitetable AS s
INNER JOIN techtable AS t
    ON s.sitename = t.sitename
UNION
SELECT 
    'SumTCHTraffic' AS SumOf,
    sum(CASE WHEN t.BTS_TYPE = '2G' THEN s.SumTCHTraffic ELSE 0 END) AS [Total 2G],
    sum(CASE WHEN t.BTS_TYPE = '3G' THEN s.SumTCHTraffic ELSE 0 END) AS [Total 3G],
    sum(CASE WHEN t.BTS_TYPE = '4G' THEN s.SumTCHTraffic ELSE 0 END) AS [Total 4G]
FROM sitetable AS s
INNER JOIN techtable AS t
    ON s.sitename = t.sitename;