Comma separated values in one column - SQL SERVER

Question

Customer Table
--------------
ID   Name
1   James
2   Peter

Order Table
---------------
OrderId  CustId
100     1
101     1
102     2

How can I write a query that returns something like this

ID,Name,ListofOrders
1,James,"100,101"
2,Peter,"102"

In Sybase I had a function called LIST which I could use but I dont find a similar function in SQL SERVER

Answer 1

Please try:

select ID, [Name],
(select OrderID+',' from OrderTable where CustID=ID
group by OrderID for xml path('')) AS ListOfOrders
From CustomerTable

Answer 2

Create a User Defined Function as shown below

CREATE FUNCTION [dbo].[CommaSeperatedOrderIDs](@CustId INT) returns varchar(Max)
AS  
BEGIN   

DECLARE @CommaSeperatedValues VARCHAR(MAX)
SELECT @CommaSeperatedValues = COALESCE(@CommaSeperatedValues+',' , '') + OrderID
FROM OrderTable WHERE CustId = @CustId
RETURN @CommaSeperatedValues

END

And then,

select ID, [Name], ([dbo].[CommaSeperatedOrderIDs](ID)) AS ListofOrders
From CustomerTable