Using COALESCE in SQL view
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: Modified answer to include creation of view.
/* Set up sample data */ 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 the view */ 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 /* Demo the view */ select * from OrderView go /* Clean up after demo */ drop view OrderView drop table Customers drop table Orders go