Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel