問題描述

我需要從多個(gè)表創(chuàng)建一個(gè)視圖.視圖中的一列必須由表中的多行組成，作為帶有逗號(hào)分隔值的字符串.

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.

這是我想做的事情的一個(gè)簡(jiǎn)化示例.

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

Customers:
CustomerId int
CustomerName VARCHAR(100)

Orders:
CustomerId int
OrderName VARCHAR(100)

客戶和訂單之間存在一對(duì)多關(guān)系.所以給定這個(gè)數(shù)據(jù)

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'

我想要這樣的視圖:

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

這樣每個(gè)人都會(huì)出現(xiàn)在表格中，無論他們是否有訂單.

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

我有一個(gè)需要轉(zhuǎn)換為該視圖的存儲(chǔ)過程，但您似乎無法在視圖中聲明參數(shù)和調(diào)用存儲(chǔ)過程.關(guān)于如何將此查詢放入視圖中的任何建議?

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:修改答案以包含視圖的創(chuàng)建.

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

這篇關(guān)于在 SQL 視圖中使用 COALESCE的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！