I need to create a view from several tables. One of the columns in the view will have to be composed out of a number of rows from one of the table as a string with comma-separated values.

Here is a simplified example of what I want to do.

Customers:
CustomerId int
CustomerName VARCHAR(100)

Orders:
CustomerId int
OrderName VARCHAR(100)

There is a one-to-many relationship between Customer and Orders. So given this data

Customers
1 'John'
2 'Marry'

Orders
1 'New Hat'
1 'New Book'
1 'New Phone'

I want a view to be like this:

Name     Orders
'John'   New Hat, New Book, New Phone
'Marry'  NULL

So that EVERYBODY shows up in the table, regardless of whether they have orders or not.

I have a stored procedure that i need to translate to this view, but it seems that you cant declare params and call stored procs within a view. Any suggestions on how to get this query into a view?

CREATE PROCEDURE getCustomerOrders(@customerId int)
AS
   DECLARE @CustomerName varchar(100)
   DECLARE @Orders varchar (5000)

   SELECT @Orders=COALESCE(@Orders,'') + COALESCE(OrderName,'') + ',' 
   FROM Orders WHERE CustomerId=@customerId

   -- this has to be done separately in case orders returns NULL, so no customers are excluded
   SELECT @CustomerName=CustomerName FROM Customers WHERE CustomerId=@customerId

   SELECT @CustomerName as CustomerName, @Orders as Orders
Edit
Report