February 10, 2005 at 1:41 pm
I am having trouble creating a query that ouputs each of the children associated with a parent. Here is a simplification of my tables:
tblParent tblChild
Parent_ID Child_ID Parent_ID
A 1 A
B 2 A
C 3 A
4 B
5 C
6 C
Desired Result:
Parent_ID Children
A 1,2,3
B 4
C 5,6
The desired result set would have the parent as well as each of the parent's children concatenated together. Any ideas how to do this in SQL? I could do it in code, but unfortunately It's not an option in this case.
February 10, 2005 at 1:58 pm
CREATE FUNCTION dbo.fnGetChilds (@Parent_ID as varchar(10))
RETURNS varchar(4000)
AS
Declare @Items as varchar(4000)
set @Items = ''
Select @Items = @Items + CAST(Child_id as varchar(10)) + ', ' from dbo.tblChild where Parent_ID = @Parent_ID
SET @Items = LEFT(@Items, ABS(LEN(@Items) - 2))
Return @Items
GO
Select Parent_ID, dbo.fnGetChilds (Parent_ID) as Childs from tblParents
February 10, 2005 at 2:10 pm
Can it be done in a query without the stored procedure?
February 10, 2005 at 2:18 pm
No you have to use the function. You have to declare a variable to concatenate the items togheter. But you can use the function in a Select so you won't be too limited by that.
February 11, 2005 at 12:14 am
Here is a way to get your desired results.
Does not use the UDF, but it does use a temporary table whilst it generates the list of children per parent.
The use of the UDF is much more elegant.
Robert
/* construct table and data */
create table tblParent (Parent_ID char(5))
create table tblChild (Child_ID char(5), Parent_ID char(5) )
insert tblParent values ('A')
insert tblParent values ('B')
insert tblParent values ('C')
insert tblParent values ('D') /*this parent without children*/
insert tblChild values ( '1', 'A')
insert tblChild values ( '2', 'A')
insert tblChild values ( '3', 'A')
insert tblChild values ( '4', 'B')
insert tblChild values ( '5', 'C')
insert tblChild values ( '6', 'C')
/* ###### what you use instead #### */
/* create a temporary table to hold each parent and list of children */
if object_id('tempdb..##ParentChilddenormalised') is not null
drop table ##ParentChilddenormalised
select Parent_ID, convert(varchar(30),'') as Children
into ##ParentChilddenormalised
from tblParent
where 1 = 0
declare @ThisParentID char(5)
declare @Children varchar(1000)
/* load it with list of parents */
insert ##ParentChilddenormalised (Parent_id)
SELECT Parent_ID from tblParent
/* get a parent where we havent gotten a list of children yet */
select top 1 @ThisParentID = Parent_ID
from ##ParentChilddenormalised
where Children is null
/* loop for each parent id */
while @@ROWCOUNT 0
begin
/* generate a list of children */
set @children = ''
select @children = @children +', ' + Child_ID
from tblChild where Parent_ID = @ThisParentID
/* write the list back into the temp table */
update ##ParentChilddenormalised
set Children = @children
where Parent_ID = @ThisParentID
/* get the next parent */
select top 1 @ThisParentID = Parent_ID
from ##ParentChilddenormalised
where Children is null
end
/* show each parent and list of children */
select * from ##ParentChilddenormalised
February 11, 2005 at 6:26 am
The UDF is more elegant, faster and has the added advantage of not building a global ##temp table which can cause big locking problems.
February 11, 2005 at 6:41 am
Drum rolls, please....
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply