MS SQL statement to combine multiple names

I have written a query where the output gives me a family name and a student first name.  If the family has more than one student, it repeats the family information for each student. I would like to have the query output such that there is one set of family information but multiple first names with commas between them.
Ex.

John and Jane Doe  
111 Nowhere st.
Nowhere,CO
Billy

John and Jane Doe
111 Nowhere st.
Nowhere,CO
Susie

What I would Like:

John and Jane Doe
111 Nowhere st.
Nowhere,CO
Billy, Susie
smantzDirector of TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

smantzDirector of TechnologyAuthor Commented:
I thought I might give the actual query I have thus far.  The Max funtion is for getting a single result for a multiple e-mail address with possible nulls.

Select
Family,
Student,
Address1,
Address2,
Cty,
ST,
ZP,
PH1,
isnull (MAX(EmailAddress),'') AS "HOH1 EMail",
isnull (MAX(EmailAddressAlt),'') AS "HOH2 EMail"
from
(
SELECT
hhd.Name as Family,
stu.FName as Student,
hhd.Addr1 as Address1,  
hhd.Addr2 as Address2,
hhd.City as Cty,
hhd.State as ST,
hhd.Zip as Zp,
 STUFF(STUFF(STUFF(hhd.Phone1,1,0,' ('),6,0,') '),11,0,'-')as ph1,
      Case When con.numbertype='E'and sc.ord =0 and con.number is not null THEN con.Number
      END EmailAddress,
      Case When con.numbertype='E'and sc.ord = 1 and con.number is not null THEN con.Number
      END EmailAddressAlt

from Household hhd
join StudentHomeInfo hi on hi.HHID = hhd.HHID
join StudentBasic stu on stu.sno=hi.sno
join Person p on stu.hhid=p.hhid
join ContactNumbers con on p.Personid=con.Personid
join Stcontact sc on stu.sno=sc.sno
where stu.status='A'
)t
Group by
Family,
Student,
Address1,
Address2,
Cty,
ST,
ZP,
PH1

Order by
Family
0
dbbishopCommented:
Here is code I've used for something similar. I've cut some columns out of the SELECT and GROUP BY statements for simplicity. It should get you going.
SELECT	LastName,
		SUBSTRING((
					SELECT	( ', ' + FirstName )
					FROM	Individual i2
					WHERE	i1.FamilyID = i2.FamilyID
					ORDER BY FamilyID,
							FirstName
				  FOR
					XML	PATH('')
				  ), 3, 5000) AS FirstNames
FROM	Individual i1
GROUP BY FamilyID,
		LastName

Open in new window

0
David ToddSenior DBACommented:
Hi,

I'd write a function to do this:
-- Using a scaler function to return the comma delimited list of Students
if 
	object_id (N'dbo.concatStudent' ) is not null
	drop function dbo.concatStudent 
go

create function dbo.concatStudent ( @HouseholdID int)
returns nvarchar( 4000 )
as begin
	declare @fields nvarchar( 4000 )
	set @fields = null
	select @fields = coalesce( @fields + ', ', '' ) + rtrim( stu.FName )
	from dbo.StudentBasic stu
	inner join dbo.StudentHomeInfo hi
		  on hi.sno = stu.sno
	where
		hi.HHID = @HouseholdID
	return isnull( @fields, '' )
end --function
;

go

-- and call it like this:
select
	hhd.Name as Family
	, dbo.concatStudentn( hhd.HHID ) as Students
from dbo.Household hhd
;

Open in new window


HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hnasrCommented:
I have a table B (a, b, x)
data: assume a=LastName, b=FirstName, x=address. This can be extended to x1, x2, x3 for address parts
a      b      x
1      1      1
1      2      1
2      1      2

Try this query, by modifying field names and adding other similar fields, for each last name, in group by:
select x.a, x.x, replace(stuff(replace(replace((select ',',y.b from B as y  where y.a =x.a for xml path('')),'<b>',''),'</b>',''),1,1,''),',',', ') As 'FirstNames'
from B AS x group by x.a, x.x;
Result:
x	FirstNames
1	1, 2
2	1

Open in new window

0
hnasrCommented:
Here is a try with your example data using a temp table:

create table #tt (lastName nvarchar(25), address1 nvarchar(25), address2 nvarchar(25),firstName nvarchar(25));
insert into #tt values('John and Jane Doe','111 Nowhere st.','Nowhere,CO','Billy');
insert into #tt values('John and Jane Doe','111 Nowhere st.','Nowhere,CO','Susie');
select * from #tt;
select  x.lastName, x.address1, x.address2, replace(stuff(replace(replace((select ',',y.firstName from #tt as y  where y.lastName = x.lastName and y.address1 = x.address1 and y.address2 = x.address2 for xml path('')),'<firstName>',''),'</firstName>',''),1,1,''),',',', ') As 'FirstNames'
from #tt AS x group by x.lastName, x.address1, x.address2;
drop table #tt;

Result:
lastName	address1	address2	firstName
John and Jane Doe	111 Nowhere st.	Nowhere,CO	Billy
John and Jane Doe	111 Nowhere st.	Nowhere,CO	Susie

lastName	address1	address2	FirstNames
John and Jane Doe	111 Nowhere st.	Nowhere,CO	Billy, Susie

Open in new window

0
sameer2010Commented:
Since you are producing multiple rows, I am sure there is a correlation available between these rows. I would suggest that you modify the query producing these rows and use
select familyname, family address, stuff((select ','+firstname from your_table where familyname=b.familyname for xml path('')),1,'') from your_family_table b

Open in new window

0
dbbishopCommented:
While dtodd's suggestion of a function is valid, and I've used that methodology for years (I still have a similar function even today), if you compare it with using the FOR XML PATH('') logic, the statistics will typically show considerably better performance.

Whichever method you use SNO must be indexed. If you are using a version of SQL Server that allows INCLUDE columns in your indexes, and this a frequently run query, I'd include FirstName in the index.

In the query I provided, FamilyID would be replaced with SNO as the column name, and Individual would be replaced with StudentBasic as the table name.
0
smantzDirector of TechnologyAuthor Commented:
Hi everyone,
Not to ignore anyone but, this one person technology department has been busy spending most of today finding parts for an aging voice-mail system.  Hopefully, I will have a chance to try some of these solutions and see what makes sense.  One question I have is how and where exactly does the substring routine fit  in my query above.  What exactly do the i1 and i2 come from, especially the i2?  can you nest where statements in a query and does the where in:

WHERE      i1.FamilyID = i2.FamilyID  represent a join.

Once again, excuse my novice ability, but I really don't spend a lot of time writing queries (although I can spend hours doing them).
-SM
0
sameer2010Commented:
Yes, the above does represent join. ANSI join would be with explicit JOIN statement
0
dbbishopCommented:
The SUBSTRING removes the leading ', ' from the concatenated FirstNames string. As I indicated in a subsequent post, FamilyID would be replaced with SNO.
I1 and i2 are aliases (much like your use of hi. and stu.) You could use StudentBasic s1 and StudentBasic s2 in place of Individual i1 and Individual i2, and replace FirstName with FName.

This is actually a correlated sub query, where the results of the sub query are dependent on data from the outer query (thus, in your case if revised to read: s1.SNO = s2.SNO, says the SNO of the sub query has to match the SNO of the inner query. hth.
0
dbbishopCommented:
As I look more at it, I am not sure the sno is what you want to join on. The StudentBasic table contains the FName, which is what you want strung together, but I assume sno is a primary key, thus each student has their own student number (sno). If that is the case, I think HHID has to be the connecting column. Assuming that, the following should work:
SELECT	Family,
		FirstNames,
		Address1,
		Address2,
		Cty,
		ST,
		ZP,
		PH1,
		ISNULL(MAX(EmailAddress), '') AS "HOH1 EMail",
		ISNULL(MAX(EmailAddressAlt), '') AS "HOH2 EMail"
FROM	(
		SELECT	hhd.Name AS Family,
				FirstNames,
				hhd.Addr1 AS Address1,
				hhd.Addr2 AS Address2,
				hhd.City AS Cty,
				hhd.State AS ST,
				hhd.Zip AS Zp,
				STUFF(STUFF(STUFF(hhd.Phone1, 1, 0, ' ('), 6, 0, ') '), 11, 0, '-') AS ph1,
				CASE WHEN con.numbertype = 'E'
						  AND sc.ord = 0
						  AND con.number IS NOT NULL THEN con.Number
				END EmailAddress,
				CASE WHEN con.numbertype = 'E'
						  AND sc.ord = 1
						  AND con.number IS NOT NULL THEN con.Number
				END EmailAddressAlt,
				SUBSTRING((
							SELECT	( ', ' + stu2.FName )
							FROM	StudentBasic stu2
							WHERE	stu2.sno = stu1.sno
							ORDER BY stu2.sno,
									stu2.FName
							  FOR XML	PATH('')
						  ), 3, 5000) AS FirstNames
		  FROM	Household hhd
		  INNER JOIN StudentHomeInfo hi
		  ON	hi.HHID = hhd.HHID
		  INNER JOIN StudentBasic stu1
		  ON	stu1.sno = hi.sno
		  INNER JOIN Person p
		  ON	stu1.hhid = p.hhid
		  INNER JOIN ContactNumbers con
		  ON	p.Personid = con.Personid
		  INNER JOIN Stcontact sc
		  ON	stu.sno = sc.sno
		  WHERE	stu.status = 'A'
		) t
GROUP BY Family,
		FirstNames,
		Address1,
		Address2,
		Cty,
		ST,
		ZP,
		PH1

Open in new window

0
David ToddSenior DBACommented:
Hi,

Summary of what Mr Bishop just posted:
We need schemas of the tables involved and a larger sample of data.

Regards
  David
0
smantzDirector of TechnologyAuthor Commented:
I will endeavor to provide the schemas for the above tables.  As for Data samples (I'll assume you refer to the actual raw data in various tables, might be more difficult as this material is from our student information system and presents certain privacy issues.  If I am incorrect, please let me know.
I will say, that a couple of the joins and tables used are not directly involved other than finding a way to link primary keys/secondary keys.
I will give Mr. Bishop's example a try.
Finally, what if their are more than two students in a family (occasionally happens)?
-SM
0
hnasrCommented:
After 13 comments, issue must have been resolved. It will drag on for long time.
I expect a feed back on either comment to know if we bookmark the question or forget about it.

I am expecting a comment on either of these 2 comments.
http:#a39465668
http:#a39465924

What is expected from poster is an example like what I presented in comment http:#a39465924 to use instead of us recreating environment which wastes time for both of us.
0
dbbishopCommented:
Did you try this code? Unless I mistyped something, it should run directly and give you a starting point. Not sure the entire schema is required unless you run into issues with the code that has been presented so far.
0
smantzDirector of TechnologyAuthor Commented:
Well,
I received an error

Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'FOR'.

I'm guessing SQL 2000 doesn't support the "FOR XML"  

Yes/No?

-SM
0
smantzDirector of TechnologyAuthor Commented:
To hnasr:  My apologies as I didn't realize there was a limit to these posts.  Your posts above are intriguing  however,  this may all be for naught as this is SQL 2000.  Again my deepest apologies to everyone as I generally include all software involved.
0
dbbishopCommented:
Use dtodd's post regarding creating a function.
0
hnasrCommented:
smantz,

I don't say that 13 comments is a limit. A question presented properly, and a useful feedback speeds up its resolution.

What feedback can I get out of "this may all be for naught as this is SQL 2000"?
0
smantzDirector of TechnologyAuthor Commented:
Hi there,

Well it's not all for naught if one learns new things.  If a resolution can be found that works within the boundries of the software then it is great however, my failure to identify what software I'm using and knowing it's limitations, lead contributors to provide solutions that can't work at the moment ie; SQL 2000 doesn't seem to support the "For XML" part of the statement.  ( According to some online sites, it appears this funtionality doesn't begin until SQL 2005 )
I extremely value the expertise and the time individuals put into the problem and I don't want to waste there time. Hopefully this helps explain my comments.
0
smantzDirector of TechnologyAuthor Commented:
I greatly appreciate the hard work and contributions everyone has made.  I have more work to do and may do a repost with more detailed questions. I will attempt the scalar function and if I run into to many problems, I may resort to a different solution.
Thanks
-SM
0
David ToddSenior DBACommented:
Thank db bishop!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.