Use SQL Server CTE to retrieve multiple result sets

Question

I was wondering how I can retrieve multiple result sets based on one CTE? Something like what I have below - but obviously this doesn't work.

Does anyone know how I can get these 2 (or more) sets of data, based on that one CTE? (more, as in that it would be nice to get the total record count from this same CTE as well.)

;WITH CTE AS  
(
  SELECT 
      Column1, Column2, Column3 
  FROM 
      Product 
  WHERE 
      Name LIKE '%Hat%' AND Description Like '%MyBrand%'
)
SELECT DISTINCT CategoryId FROM CTE
SELECT DISTINCT BrandId FROM CTE

Answer 1

A CTE only exists for the query immediately following it, so it's not possible to use it for two separate select statements. You'll either need to persist the data in something like a temp table, or construct/invoke the CTE twice.