我需要从几个表中创建一个视图.视图中的一列必须由表中的一行以多行形式组合,并以逗号分隔的值作为字符串.
这是一个简单的例子,我想做什么.
Customers: CustomerId int CustomerName VARCHAR(100) Orders: CustomerId int OrderName VARCHAR(100)
客户与订单之间存在一对多的关系.所以给出这个数据
Customers 1 'John' 2 'Marry' Orders 1 'New Hat' 1 'New Book' 1 'New Phone'
我想要这样看待：
Name Orders 'John' New Hat,New Book,New Phone 'Marry' NULL
所以每个人都会出现在表中,无论他们是否有订单.
我有一个存储过程,我需要翻译到这个视图,但似乎你不能声明参数和调用存储过程在视图中.关于如何将这个查询转换成视图的任何建议？
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
解决方法
编辑：修改答案包括创建视图.
/* 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,'') 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
