How to insert average for each column in SQL Server table with a condition

I have created a temporary table with dynamic columns. These columns are the values in a master table.

Say,

Id |Name
1  |Prepaid
2  |Postpaid

so the temporary table columns will be

Id Prepaid Postpaid

In this case the columns are dynamic.

consider the below code for temp table:

declare @temp as table (Id int, Prepaid float, postpaid float)

insert into @temp values(1,100,200)
insert into @temp values(1,10,500)
insert into @temp values(1,-100,-100)
insert into @temp values(1,10,200)
insert into @temp values(1,100,-100)
insert into @tem开发者_如何学JAVAp values(1,150,560)
insert into @temp values(1,90,200)

I need to insert a row with average of each column.

Average should be calculated taking the rows which meet the condition, i.e, the column should not contain a value -100.

I need to insert a row with average values like for the above values.

Id       PrepaidAvg      PostPaidAvg  
1     76.6666666666667  320

Hard to determine exactly what result you expect, but here is one try:

SELECT 
  Id, 
  PrepaidAvg = AVG(CASE WHEN Prepaid > 0 THEN Prepaid ELSE NULL END),
  PostpaidAvg = AVG(CASE WHEN postpaid > 0 THEN postpaid ELSE NULL END)
FROM @temp
GROUP BY Id;

If this is off you can get more elaborate:

;WITH prepaid AS
(
  SELECT Id, PrepaidAvg = AVG(Prepaid)
    FROM @temp WHERE Prepaid > 0
), postpaid AS
(
  SELECT Id, PostpaidAvg = AVG(postpaid)
    FROM @temp WHERE postpaid > 0
)
SELECT Id = COALESCE(prep.Id, postp.Id),
  prep.PrepaidAvg,
  postp.PostpaidAvg
FROM prepaid AS prep
FULL OUTER JOIN postpaid AS postp
ON prep.Id = postp.Id;

Use this query

insert into @temp 
select avg(Id), avg(Prepaid), avg(postpaid)
from @temp 
where Prepaid <> -100 and postpaid <> -100