MS SQL statement to combine multiple names

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

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

Yes, the above does represent join. ANSI join would be with explicit JOIN statement

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.

Hi,

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

Regards
  David

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

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.

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.

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

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.

Use dtodd's post regarding creating a function.

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"?

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.

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

Thank db bishop!