Group elements of a column into mulitple subgroups SQL

I am looking at different breeds of cattle and their AnimalTypeCode , BreedCateoryID and resultant Growth.

I have the following query

SELECT DATEPART(yyyy,[KillDate])
        ,[AnimalTypeCode]
        ,AVG([Growth])
        ,[BreedCategoryID]
  FROM [dbo].[tblAnimal]
  WHERE (AnimalTypeCode='C'
        or AnimalTypeCode= 'E')
    GROUP BY DATEPART(yyyy,[KillDate])
        ,[AnimalTypeCode]
        ,[BreedCategoryID]
    GO

This query is good and gives me almost what I want, but BreedCategoryID is numbered 1 through 7 and I would like to group them:

(1 = Pure Dairy),
(2 and 3 = Dairy)
(4, 5, 6 and 7 = Beef) 

So instead of getting the mean Growthrate for each BreedCategoryID I would like to get the average for Pure Dairy, Dairy, and Beef.

Any help greatly appreciated!

Answer

You can assign a new “variable” using cross apply in the from clause:

SELECT YEAR(KillDate]), a.AnimalTypeCode, v.grp,
        AVG([Growth])
FROM [dbo].[tblAnimal] a CROSS APPLY
     (VALUES (CASE WHEN a.BreedCategoryID IN (1) THEN 'Pure Dairy'
                   WHEN a.BreedCategoryID IN (2, 3) THEN 'Dairy'
                   WHEN a.BreedCategoryID IN (4, 5, 6, 7) THEN 'Beef'
              END)
     ) as v(grp)
WHERE a.AnimalTypeCode IN ('C', 'E')
GROUP BY YEAR(KillDate]), a.AnimalTypeCode, v.grp;

Note that I also introduced table aliases and qualified all the column references.