Showing Child Rows as a comma delimited string.

[NoCoolHandle]

I bumped into a new one for me the other day that I thought I would share..

Imagine 2 tables (Family and firstnames)
Each Family can only have one LastName, but each Family could have more than one firstname (representing differ the different family members)

I.E. A Family of Smith with 4 members could be represeted a couple of ways

LastName FamilyMembers
Smith John, Mary, Jane, Jill

Or

LastName FirstName
Smith John
Smith Mary
Smith Jane
Smith Jill

In the case of display simplicity the first option is cleaner, but sql really wants you to see the second option.

So...
The solution might be to bulid a cursor (as in

http://www.tek-tips.com/faqs.cfm?fid=2146

) and 1 row at a time append the new firstname to the last FirstName with a comma stuck between, but that is intensive.. and bottom line - CURSORS have no place in efficent SQL period (IMHO)

Here is what I found would work (full scripts for you to play with at the bottom) - remember I HATE cursors.. they just arn't necessary and place locks on resources like they are going out of fasion.

1 row - displayed with commas between each family member

Declare @v varchar(8000)
Select @v=''
Select @v = @v + Firstname + ', ' From Firstnames where mid = 1
select Left(@v,Len(@v)-1)

The interesting thing is that setting the variable to being equal to itself + the column value causes it to process the entire rowset.
Select [blue]@v[/blue] = [blue]@v + Firstname + ', '[/blue] From Firstnames
would return something like
John, Mary, Jane, Jill,

The question becomes how do you make the variable work for each Family rather than 1 member at a time.. (step in the UDF)

Create Function fn_ReturnFamilyMembers
(@Mid int)
Returns Varchar(8000)
as
begin
 Declare @v varchar(8000)
 Select @v=''
 Select @v = @v + Firstname + ', ' 
   From Firstnames where 
   mid = @mid
 select @v= Left(@v,Len(@v)-1)
Return @v
End

Then to use it just pass in the memberID
e.g.

Select Mid,LastName,dbo.fn_ReturnFamilyMembers(mid) as Members
From Familys
Order by LastName
[code]

Would return something like
[Quote]
Mid         lastname   Members                        
----------- ---------- ------------------------------ 
9           Apple      Granny
5           Cruz       Solo
4           Dao        PIede, Dinggone
8           Dooshee    Baghe, Quick
2           Jones      Sally, Rafiel, Sierra
7           O'Guin     Paddy
3           Patel      Tom, Sky, Moon
1           Smith      Bob, Mary, Tom, Jim, Emily
6           Stoner     Duice1, Duice2
10          Xaolin     Zeta, Beta, Alpha
[/Quote]


FULL SCRIPTS _ CUT AN PASTE INTO QA

[Code]

Create database Crap
go
Use Crap
go
create table Familys 
(mid int identity primary key,
 LastName varchar(300) not null)
create table firstnames
(mid int foreign key references Familys(mid),
 FirstName varchar(300))
go
Insert into Familys (lastname) values('Smith')
	insert into FirstNames (Mid,Firstname) values (1,'Bob')
	insert into FirstNames (Mid,Firstname) values (1,'Mary')
	insert into FirstNames (Mid,Firstname) values (1,'Tom')
	insert into FirstNames (Mid,Firstname) values (1,'Jim')
	insert into FirstNames (Mid,Firstname) values (1,'Emily')
Insert into Familys (lastname) values('Jones')
	insert into FirstNames (Mid,Firstname) values (2,'Sally')
	insert into FirstNames (Mid,Firstname) values (2,'Rafiel')
	insert into FirstNames (Mid,Firstname) values (2,'Sierra')
Insert into Familys (lastname) values('Patel')
	insert into FirstNames (Mid,Firstname) values (3,'Tom')
	insert into FirstNames (Mid,Firstname) values (3,'Sky')
	insert into FirstNames (Mid,Firstname) values (3,'Moon')
Insert into Familys (lastname) values('Dao')
	insert into FirstNames (Mid,Firstname) values (4,'PIede')
	insert into FirstNames (Mid,Firstname) values (4,'Dinggone')
Insert into Familys (lastname) values('Cruz')
	insert into FirstNames (Mid,Firstname) values (5,'Solo')
Insert into Familys (lastname) values('Stoner')
	insert into FirstNames (Mid,Firstname) values (6,'Duice1')
	insert into FirstNames (Mid,Firstname) values (6,'Duice2')
Insert into Familys (lastname) values('O''Guin')
	insert into FirstNames (Mid,Firstname) values (7,'Paddy')
Insert into Familys (lastname) values('Dooshee')
	insert into FirstNames (Mid,Firstname) values (8,'Baghe')
	insert into FirstNames (Mid,Firstname) values (8,'Quick')
Insert into Familys (lastname) values('Apple')
	insert into FirstNames (Mid,Firstname) values (9,'Granny')
Insert into Familys (lastname) values('Xaolin')
	insert into FirstNames (Mid,Firstname) values (10,'Zeta')
	insert into FirstNames (Mid,Firstname) values (10,'Beta')
	insert into FirstNames (Mid,Firstname) values (10,'Alpha')

select M.*,firstname from Familys M left outer join FirstNames F on M.mid = f.mid

Declare @v varchar(8000)
Select @v=''
Select @v = @v + Firstname + ', ' From Firstnames where mid = 1
select Left(@v,Len(@v)-1)
go
Create Function fn_ReturnFamilyMembers
(@Mid int)
Returns Varchar(8000)
as
begin
 Declare @v varchar(8000)
 Select @v=''
 Select @v = @v + Firstname + ', ' 
   From Firstnames where 
   mid = @mid
 select @v= Left(@v,Len(@v)-1)
Return @v
end
go
Select Mid,LastName,dbo.fn_ReturnFamilyMembers(mid)as Members
From Familys
Order by LastName
go
drop table firstnames
drop table familys
drop function fn_ReturnFamilyMembers

For what it is worth...

I just thought it interesting and can't rember seeing it written up anywhere.

 

[bertrandkis]

The key here is to understand how assigment into variables works.Since we ar on the subject, the following is an Excerpt from my SQL Bible:
--A variable is a tempory memory location where a program stores data as it runs
--2 Types of variables in MSSQL: Local and Global variables
--A variable declaration consists of @(for local variable), @@ (for global variables)
--the variable name and data type. The data type determines the type and amount of data the variable can store.
--After declaration, all variables are initialized as NULL.
--Values are assigned to variables using the Set or Select Keywords.
declare @var char(3),@name varchar(80)
set @var=56
select @var
--This statement will store the last record into the variable since the value in the variable is over written with each record returned by the select statement.
select @name=au_fname+' '+au_lname from authors order by au_lname
select @name

--this statement will concatenate all values in the query result and store
--them in the variable as a single row.
Declare @names varchar(8000)
Set @names='' --Inintialize varible to remove null value
select @names=@names+','+au_fname+' '+au_lname from authors order by au_lname
select @names

--this assigment will generate an error
Set @names=(select @names=@names+','+au_fname+' '+au_lname from authors)

Adios.
Bertrandkis

 

[NoCoolHandle]

The key is to combine the concept of the variable with the POWER of a UDF.

No cursors, no row by row coding, just a simple function being passed into a simple query.


It is all about simplicity and effeciency.

Select Mid,LastName,dbo.fn_ReturnFamilyMembers(mid)as Members
From Familys
Order by LastName

 

[vongrunt]

FYI this is ol' trick - we post it almost any time someone wants to do concat aggregate or something. Once every week or so

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bertrandkis]

Hey NoCoolHandle,
Your UDF script is good but it's limited to the problem you wanted to solve because you have hard coded the column names and tables in the UDF "Select @v = @v + Firstname + ', ' From Firstnames where mid = @mid" so it cannot be used in a different scenario. However understanding how assigment into variables works will allow you to use the knowledge in any scenario. Here is an example where I used the select Keyword to concatenate rows. Below is a table containing Learners records:
Learner Course code Marks obtained
--------------------------------------------------------
Leticia Eksteen 9042 52
Leticia Eksteen 9122 48
Leticia Eksteen 9147 85
Victoria Collins 9042 70
Victoria Collins 9122 60
Victoria Collins 9147 80
John Best 9042 78
John Best 9122 55
John Best 9147 66
John Best 9991 100
The manager want to see the records in a cross-tab format like this:
Learner code_9042 code_9122 code_9147 Code_9991
L.Eksteen 52 48 85 NULL
V.Collins 70 60 80 NULL
J.Best 78 55 66 100
Can you use your User Defined Function to create Cross-tab? Remember that the column headers are built dymanically as not all learners will do the same number of courses. I solved this problem using a statement like this in my code: Select @strValues=@strValues+','+ ..........
and I did not need a UDF. I would like to urge people to give it a shot and let's see the different scripts.