You can use derived table

Select fname, lname
  from ( Select *  from Customer where state='CA' ) csrCustomer
  where fname like 'Mich%'
    and lname like 'Levy%'
  Group by fname, lname

Or you can use temp table

Select * INTO #csrCustomer
  from Customer  where state='CA'
Select fname, lname
  from tempdb..#csrCustomer
  where fname like 'Mich%'
    and lname like 'Levy%'
  Group by fname, lname

>I know this question has been ask before but I can't find the answer in the PUTM Search. Hope you can help...
>
>What is the equivilent in SQL Server for the following:
>
>Select *
> from Customer
> where state='CA'
> into cursor csrCustomer
>
>Select fname, lname
> from csrCustomer
> where fname like 'Mich%'
> and lname like 'Levy%'
> Group by fname, lname
>
>/* Hint-Hint */
>
>I know this is a lame example (unless you're Micheal Levy then it's a cool example) but it's what's being attempted in a more complicated form - so nested selects won't do the job for me.
>
>I tried playing with SQL Server Cursors but apperantely you can only fetch one record at a time - that blows!
>
>Any help is appreciated - TIA