Using COALESCE in SQL view

Question

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

Rasia

What version of SQL Server do you have? – Opinicus

Answer 1

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

Paapanen

Sweet, that worked like magic! I just added DISTINCT to the first select statement and its ready to serve. Thanks! – Rasia

The only problem with my this sql statement is that you cannnot make a view with such a statement indexed as CROSS APPLY not allowed for indexed views. Any suggestion on how to transform a view with this select into an index friendly view? Thanks! – Rasia

Answer 2

In SQL Server 2008, you can take advantage of some of the features added for XML to do this all in one query without using a stored proc:

 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

Opinicus

Like it David...use this XML method all the time to build strings. – Bowman

Nice short, succinct example - I was able to apply it immediately. Cheers! – Giacinta