WITH t as ( select '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3 union all select '2000152002' chitemseq, '40' chitem1, '70' chitem2, '20' chitem3 union all select '2000152003' chitemseq, '5.44E+07' chitem1, '3.66E+07' chitem2, '3.24E+07' chitem3 ) select t.*, case WHEN ChItemseq = 2000152001 THEN CAST((CAST(ChItem1 AS FLOAT)+CAST(ChItem2 AS FLOAT)+CAST(ChItem3 AS FLOAT) / 3 ) AS numeric(12,1)) WHEN ChItemseq = 2000152002 THEN CAST((CAST(ChItem1 AS FLOAT)+CAST(ChItem2 AS FLOAT)+CAST(ChItem3 AS FLOAT) / 3 ) AS INT ) WHEN ChItemseq = 2000152003 THEN UPPER(CAST(CAST( ROUND( (CAST(ChItem1 AS FLOAT)+ CAST(ChItem2 AS FLOAT)+ CAST(ChItem3 AS FLOAT)) / 3 ,FLOOR(LOG10((CAST(ChItem1 AS FLOAT)+ CAST(ChItem2 AS FLOAT)+ CAST(ChItem3 AS FLOAT)) / 3)) * -1 + 1)AS FLOAT) AS VARCHAR)) END FROM T
-- MSSQL -- WITH t AS ( SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '202' chitem3 UNION ALL SELECT '2000152002', '40', '70', '20' UNION ALL SELECT '2000152003', '5.44E+07', '3.66E+07', '3.24E+07' ) SELECT chitemseq , chitem1, chitem2, chitem3 , x , y , REPLACE(ROUND(x, y), 'e+0', 'E+') z FROM (SELECT chitemseq , chitem1, chitem2, chitem3 , ( CAST(chitem1 AS FLOAT) + CAST(chitem2 AS FLOAT) + CAST(chitem3 AS FLOAT) ) / 3 AS x , CASE chitemseq WHEN '2000152001' THEN 1 WHEN '2000152002' THEN 0 WHEN '2000152003' THEN -5 END AS y FROM t ) a ;
WITH t AS ( SELECT '2000152001' chitemseq, '326' chitem1, '284' chitem2, '203' chitem3 UNION ALL SELECT '2000152002', '40', '70', '20' UNION ALL SELECT '2000152003', '5.44E+07', '3.66E+07', '3.24E+07' ) SELECT chitemseq , chitem1, chitem2, chitem3 , x , CASE chitemseq WHEN '2000152001' THEN CAST(CAST(x AS NUMERIC(12, 1)) AS VARCHAR) WHEN '2000152002' THEN CAST(CAST(x AS NUMERIC(12, 0)) AS VARCHAR) WHEN '2000152003' THEN REPLACE(ROUND(x, -5), 'e+0', 'E+') END z FROM (SELECT chitemseq , chitem1, chitem2, chitem3 , ( CAST(chitem1 AS FLOAT) + CAST(chitem2 AS FLOAT) + CAST(chitem3 AS FLOAT) ) / 3 AS x FROM t ) a ;