Conditional column select

Is there a statement to change the column in a select clause?

For example:

select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.

Examples

A. Use a SELECT statement with a simple CASE function

Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs
GO
SELECT Category = 
 CASE type
 WHEN 'popular_comp' THEN 'Popular Computing'
 WHEN 'mod_cook' THEN 'Modern Cooking'
 WHEN 'business' THEN 'Business'
 WHEN 'psychology' THEN 'Psychology'
 WHEN 'trad_cook' THEN 'Traditional Cooking'
 ELSE 'Not yet categorized'
 END,
 CAST(title AS varchar(25)) AS 'Shortened Title',
 price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

B. Use a SELECT statement with simple and searched CASE function
Within a SELECT statement, the searched CASE function allows values to be replaced in the result set based on comparison values. This example displays the price (a money column) as a text comment based on the price range for a book.
USE pubs
GO
SELECT 'Price Category' = 
 CASE 
 WHEN price IS NULL THEN 'Not yet priced'
 WHEN price < 10 THEN 'Very Reasonable Title'
 WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
 ELSE 'Expensive book!'
 END,
 CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

C. Use CASE with SUBSTRING and SELECT
This example uses CASE and THEN to produce a list of authors, the book identification numbers, and the book types each author has written. 
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ 
 RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
 Type = 
 CASE 
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
 END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

|||

How would I change the static text into the column that I want?

If I used this for example:

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN Price
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles

I would like my result to be like:

Category Shortened Title Price

2.9900 The Gourmet Microwave 2.9900
19.9900 Silicon Valley Gastronomi 19.9900
Popular Computing Secrets of Silicon Valley 20.0000
Popular Computing But Is It User Friendly? 22.9500