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
Please try:
select ID, [Name],
(select OrderID+',' from OrderTable where CustID=ID
group by OrderID for xml path('')) AS ListOfOrders
From CustomerTable
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
Adding full details from Sheikh Haris' link.
Given this table:
To get output like:
Use the following SQL:
SELECT field1,
Substring(convert(varchar(100),
(
SELECT (', ' + field2)
FROM #test t2
WHERE t1.field1 = t2.field1
ORDER BY field1, field2
FOR XML PATH( '' )
)), 3, 1000 )
FROM #test t1
GROUP BY field1
I added a convert function to the substring so that the WIDEMEMO field would be displayed (SQL Server)
A very simple and handy solution given on the link below.
The SQL query written on that link is in an image ...so i couldn't copy it here.
