title: How to rewrite CROSS APPLY to INNER JOIN to make the view indexed
body: <p>On a <a href="https://stackoverflow.com/questions/4081893/using-coalesce-in-sql-view">separate thread</a> I got a working example on how to translate my stored proc to a view, that will hold customer names to orders mapping, where orders are comma-separated lists of orders, including NULL for no orders. So for the table below, I need the following to appear in the view:</p>
  
  <pre><code>Name     Orders
  'John'   New Hat, New Book, New Phone
  'Marry'  NULL
  </code></pre>
  
  <p>I need to index the view, but you cant do it if the SELECT query within a view has APPLY and/or subqueries. Is it possible to translate this view to an indexed view?</p>
  
  <pre><code>create table Customers (CustomerId int, CustomerName VARCHAR(100))
  create table Orders    (CustomerId int, OrderName VARCHAR(100))
  
  insert into Customers  (CustomerId, CustomerName) select 1, 'John' union all select 2, 'Marry'
  insert into Orders     (CustomerId, OrderName)    select 1, 'New Hat' union all select 1, 'New Book' union all select 1, 'New Phone'
  go
  
  create view OrderView as 
  select c.CustomerName, x.OrderNames        
  from Customers c            
  cross apply (select stuff((select ',' + OrderName from Orders o 
        where o.CustomerId = c.CustomerId for xml path('')),1,1,'') 
        as OrderNames) x
  go
  </code></pre>