5

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tys
  • 3,592
  • 9
  • 49
  • 71
  • I have seen some examples that managed to get the count and one result set at the same time. But i couldn't find anything that produces multiple result sets. – Tys Jan 28 '15 at 21:06
  • 2
    If you need something like this then you should probably use either a table variable or a temp table, not a CTE – Lamak Jan 28 '15 at 21:07
  • I agree with @Lamak. I was going to suggest adding ' into #tempTable ' to your CTE query and using the new temp table for subsequent queries. – rwking Jan 28 '15 at 21:09
  • An ugly way that would work for this specific scenario: `;WITH CTE AS (...) SELECT 'C' AS ItemType, CategoryID FROM CTE UNION SELECT 'B', BrandID FROM CTE`. Pull the rows from your result where `ItemType='C'` to get categories, and where `ItemType='B'` for the brands. – Ed Gibbs Jan 28 '15 at 21:16
  • possible duplicate of [Use Multiple CTE](http://stackoverflow.com/questions/18924746/use-multiple-cte) – paparazzo Jan 28 '15 at 21:27
  • 1
    Not just that it can't be done. It should not be done. A CTE is just syntax is evaluated. If you going to use the results more than once then typically you should persist to #temp or table variable. – paparazzo Jan 28 '15 at 21:35
  • Yep, as i said. Clear. It was just that for some time it looked to me like something that could fit into what i wanted to accomplish. – Tys Jan 28 '15 at 21:38

1 Answers1

8

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.

Xedni
  • 3,662
  • 2
  • 16
  • 27
  • 1
    Alright, that's totally clear then. Seeing some of those examples, looked like it could be done, but, i get the point, it's just not possible. – Tys Jan 28 '15 at 21:32