1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- 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 ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 ; |