代码之家 › 专栏 › 技术社区 › kateroh

在SQL视图中使用COALESCE

sql-view coalesce sql-server-2008 tsql sql-server

9

kateroh · 技术社区 · 15 年前

我需要从几个表创建一个视图。视图中的一列必须由一个表中的多行组成,并用逗号分隔的值作为字符串。

这是我想做的一个简单的例子。

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

我有一个存储过程需要转换成这个视图,但似乎不能在视图中声明params和调用存储过程。关于如何将此查询放入视图中有何建议?

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

2 回复 | 直到 15 年前

1

9

Joe Stefanelli 15 年前

编辑 :修改的答案包括创建视图。

/* 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

2

6

David 15 年前

 SELECT CustomerName,
    STUFF( -- "STUFF" deletes the leading ', '
        ( SELECT ', ' + OrderName
        FROM Orders
        WHERE CustomerId = Customers.CutomerId
        -- This causes the sub-select to be returned as a concatenated string
        FOR XML PATH('') 
        ),
    1, 2, '' )
    AS Orders
 FROM Customers