Few ways of doing this, here is one

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TAVG TABLE

(

ID INT NOT NULL

,A NUMERIC(12,2) NOT NULL

,B NUMERIC(12,2) NOT NULL

,C NUMERIC(12,2) NOT NULL

,[AVG] INT NULL

);

INSERT INTO @TAVG(ID,A,B,C)

VALUES

(1,08.0,09.0,10.0)

,(2,10.0,25.0,26.0)

,(3,09.0,15.0,16.0);

;WITH BASE_DATA AS

(

SELECT

A.ID

,ROW_NUMBER() OVER

(

PARTITION BY A.ID

ORDER BY X.VAL DESC

) AS X_DRID

,X.VAL

FROM @TAVG A

CROSS APPLY

(

SELECT A.A UNION ALL

SELECT A.B UNION ALL

SELECT A.C

) AS X(VAL)

)

,AVG_DATA AS

(

SELECT

BD.ID

,AVG(BD.VAL) AS AVG_VAL

FROM BASE_DATA BD

WHERE BD.X_DRID < 3

GROUP BY BD.ID

)

UPDATE TA

SET TA.[AVG] = AD.AVG_VAL

FROM @TAVG TA

INNER JOIN AVG_DATA AD

ON TA.ID = AD.ID

;

SELECT

A.ID

,A.A

,A.B

,A.C

,A.[AVG]

FROM @TAVG A;

Results

ID A B C AVG

--- ------ ------------ -----

1 8.00 9.00 10.00 9

2 10.00 25.00 26.00 25

3 9.00 15.00 16.00 15