CASE
WHEN [col1] = 's' THEN '1'
WHEN [col1] = 't' THEN '2'
WHEN [col1] = 'u' THEN '3'
WHEN [col2] = 'v' THEN '4'
.......
END AS product,SUM(col3)
FROM dbo.TableA
select product
from
(
select
CASE
WHEN [col1] = 's' THEN '1'
WHEN [col1] = 't' THEN '2'
WHEN [col1] = 'u' THEN '3'
WHEN [col2] = 'v' THEN '4'
END AS product,SUM(col3) as Col3Sum
FROM dbo.TableA
) a
group by product
