AkAlan
asked on
Return all rows from child table as a comma diliminated row in parent table
I have a table called Projects and for every row in Projects, there are one or more rows (Called Site) in a related table. In my stored procedure I would like to return the Project and all the sites in a comma diliminated column
So the returned row would look like this:
ProjectName,
(Site1,Site2,Site2) as Sites
Thanks for any help.
So the returned row would look like this:
ProjectName,
(Site1,Site2,Site2) as Sites
Thanks for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LeDaouk
you have to make a function that you pass for it the ID of the project and return the string you want
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try to Create this function
USE [... Database Name ...]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[Fetch_Sites]
(
@ProjectID as numeric
)
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @Result as nvarchar(1000)
DECLARE Sites CURSOR FOR
SELECT Site FROm Sites Where Project_ID = @ProjectID
SET @Result = ''
SET @Name = ''
OPEN Sites
WHILE @@Fetch_Status = 0
BEGIN
SET @Result = @Result + @Name + ','
FETCH Companion INTO @Name
END
if len(@Result)>1
begin
SET @Result = left(@Result ,len(@Result)-1)
SET @Result = Right(@Result ,len(@Result)-1)
end
else
SET @Result=''
CLOSE Sites
RETURN @Result
END
then you can do
Sletec ProjectName,Fetch_Sites(Pr
From Projects
USE [... Database Name ...]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[Fetch_Sites]
(
@ProjectID as numeric
)
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @Result as nvarchar(1000)
DECLARE Sites CURSOR FOR
SELECT Site FROm Sites Where Project_ID = @ProjectID
SET @Result = ''
SET @Name = ''
OPEN Sites
WHILE @@Fetch_Status = 0
BEGIN
SET @Result = @Result + @Name + ','
FETCH Companion INTO @Name
END
if len(@Result)>1
begin
SET @Result = left(@Result ,len(@Result)-1)
SET @Result = Right(@Result ,len(@Result)-1)
end
else
SET @Result=''
CLOSE Sites
RETURN @Result
END
then you can do
Sletec ProjectName,Fetch_Sites(Pr
From Projects
ASKER
OK, I used the STUFF solution which EMoreau and ewangoya both suggested. I like the idea of being able to see what is going on right in the sproc, rather than have to go look at the function to see what is going on there. I am going to post my solution because I needed to include a join and I am passing a parameter to the sproc. Thanks guys, I had never heard of the STUFF Function pretty cool, I will be reusing it a lot.
STUFF((SELECT ',' + s.SiteName FROM ENG_OaSites oas
JOIN
ALL_Sites s ON oas.SiteID = s.SiteID
WHERE oas.OaNumber = @OA_Nr
ORDER BY s.SiteName FOR XML PATH ('')
)
,1,1, '') As OaSites,