Concatenating the results of a subquery into a single field 1

[glyn6]

This is my stored procedure

SELECT DISTINCT BreederID, 
	(BreederLastname + ', ' + BreederForename + ', ' + BreederTitle) as SurnameForenameTitle, 
(select breedname from breed 
where Breed.BreedID = BreederBreedLink.BreedID) SpecialistBreedList

FROM Breeder

INNER JOIN ProjBreederBreedLink ON BreederBreedLink.BreederID = Breeder.BreederID 
INNER JOIN Breed ON Breed.BreedID = BreederBreedLink.BreedID

and this gives me the following resultset
ID|SurnameForenameTitle |SpecialistBreedList
1 |Radcliffe, Stuart, Mr |Airedale Terrier
2 |Wogan, Terry, Mr |Bull Terrier
3 |MacConie, Stuart, Mr |Bull Terrier
3 |MacConie, Stuart, Mr |Collie

What I'm trying to get it to return is
ID|SurnameForenameTitle |SpecialistBreedList
1 |Radcliffe, Stuart, Mr |Airedale Terrier
2 |Wogan, Terry, Mr |Bull Terrier
3 |MacConie, Stuart, Mr |Bull Terrier, Collie

So 3 rows are returned with "Bull Terrier, Collie" returned in the SpecialistBreedList field for row 3.

Is this possible and if so how?
Thanks

 

[bborissov]

OK, from where BreederBreedLink comes from?
Typo?


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Try:

SELECT DISTINCT BreederID, 
    BreederLastname + ', ' + BreederForename + ', ' + BreederTitle as SurnameForenameTitle, 
stuff((select ', ' + breedname from breed 
where Breed.BreedID = BreederBreedLink.BreedID FOR XML PATH('')),1,2,'') as  SpecialistBreedList

FROM Breeder

INNER JOIN ProjBreederBreedLink ON BreederBreedLink.BreederID = Breeder.BreederID 
INNER JOIN Breed ON Breed.BreedID = BreederBreedLink.BreedID

PluralSight Learning Library

 

[glyn6]

Sadly it's not a typo ;-)

There's a breed table for the breed of dog, a breeder table which is a list of breeders and BreederBreedLink table is a link betwixt the 2 as apparently dog breeders can have more than one breed of dog at a time.

Tried the line
"stuff((select ', ' + breedname from breed
where Breed.BreedID = BreederBreedLink.BreedID FOR XML PATH('')),1,2,'')"
and that brings back the same result :-(

I think I've seen this done before a while back and you have to link the subselect's resultset to the main select's joins.

 

[markros]

Change my code to this:

;with Breeds as (SELECT DISTINCT BreederID, 
    (BreederLastname + ', ' + BreederForename + ', ' + BreederTitle) as SurnameForenameTitle, 
(select breedname from breed 
where Breed.BreedID = BreederBreedLink.BreedID) SpecialistBreedList

FROM Breeder

INNER JOIN ProjBreederBreedLink ON BreederBreedLink.BreederID = Breeder.BreederID 
INNER JOIN Breed ON Breed.BreedID = BreederBreedLink.BreedID )

select SurnameForeNameTitle, stuff((select ', ' + BreedName
from Breeds B1 where B1.SurnameForeNameTitle = B.SurnameForeNameTitle for xml path('')),1,2,'') as BreedNames from Breed B group by SurnameForeNameTitle

PluralSight Learning Library

 

[markros]

In my query above I didn't notice you have BreederID. Use it instead of the name in the subquery. You will need to group by 2 fields.

PluralSight Learning Library

 

[pandatime]

I believe this example can help you on the right path.

@myVar will print out 'Sue','Joe','Bob'

create table myTable(name varchar(20))
insert into myTable values ('Sue')
insert into myTable values ('Joe')
insert into myTable values ('Bob')

DECLARE @myVar VARCHAR(100)

SELECT
@myVar = IsNull(@MyVar + ',' + name, name)
FROM myTable

PRINT @myVar

 

[gmmastros]

pandatime,

I have used similar code a lot. I discovered that you can sometimes get bad results from it if you are not careful. Specifically, if you have a list of values that you are concatenating, and one of the values is NULL, you list can be wrong. There is similar weirdness with empty strings.

For example:

Declare @myTable Table(name varchar(20))
insert into @myTable values ('Sue')
insert into @myTable values ('Joe')
insert into @myTable values ('Bob')
insert into @myTable values (NULL)
insert into @myTable values ('Bill')
insert into @myTable values ('')
insert into @myTable values ('Jim')

DECLARE @myVar VARCHAR(100)

SELECT
@myVar = IsNull(@MyVar + ',' + name, name)
FROM @myTable

PRINT @myVar

The output of the query shown above is: [!]Bill,,Jim[/!]

Notice that the first 3 names are missing, and there is a weird double comma. This situation is easily corrected by using a where clause in your query.

Declare @myTable Table(name varchar(20))
insert into @myTable values ('Sue')
insert into @myTable values ('Joe')
insert into @myTable values ('Bob')
insert into @myTable values (NULL)
insert into @myTable values ('Bill')
insert into @myTable values ('')
insert into @myTable values ('Jim')

DECLARE @myVar VARCHAR(100)

SELECT
@myVar = IsNull(@MyVar + ',' + name, name)
FROM @myTable
[!]Where name > ''[/!]

PRINT @myVar

This time your output is the way you expect it to be: [!]Sue,Joe,Bob,Bill,Jim[/!]

You can even apply an order by if you want the comma delimited list to be sorted.

[tt]SELECT
@myVar = IsNull(@MyVar + ',' + name, name)
FROM @myTable
Where name > ''
Order By name[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

excellent, thanks

 

[markros]

George,

Even though the order always works for me in such constructs, it's not guaranteed to work. In other words, you can not use ORDER BY with this technique RELIABLY. There is a relevant discussion in LTD forum.

PluralSight Learning Library

 

[glyn6]

I've got farther with it now. The following brings me back a list of all the Breeders who have a record in the link table

	SELECT distinct siteUser.siteUserID,
	(siteUser.siteUserSurname + ', ' + siteUser.siteUserForename ) as SurnameForenameTitle,

	STUFF(
    (
      SELECT ',' + DogBreedName
      FROM DogBreed
       JOIN ProjBreederBreedLink ON ProjBreederBreedLink.DogBreedID = DogBreed.DogBreedID
      WHERE ProjBreederBreedLink.DogBreedID = DogBreed.DogBreedID
      AND ProjBreederBreedLink.siteUserID = siteUser.siteUserID
      FOR XML PATH('')
    ), 1, 1, '') as doglist

	FROM siteUser
	 JOIN ProjBreederBreedLink ON ProjBreederBreedLink.siteUserID = siteUser.siteUserID 
	 JOIN DogBreed ON DogBreed.DogBreedID = ProjBreederBreedLink.DogBreedID

so I get

1 |Radcliffe, Stuart, Mr   |Airedale Terrier
2 |Wogan, Terry, Mr        |Bull Terrier
3 |MacConie, Stuart, Mr    |Bull Terrier, Collie

However, if a breeder doesn't have a record in the link table then the breeder's record isn't shown, so if Wogan doesn't have a linked breed I get

1 |Radcliffe, Stuart, Mr   |Airedale Terrier
3 |MacConie, Stuart, Mr    |Bull Terrier, Collie

where I need it to return

1 |Radcliffe, Stuart, Mr   |Airedale Terrier
2 |Wogan, Terry, Mr        |
3 |MacConie, Stuart, Mr    |Bull Terrier, Collie

I've tried LEFT, RIGHT, INNER and OUTER joins to no effect.

If any one can explain exactly how this code does what it does then that'd be good too

 

[markros]

The very good link explaining what is this XML stuff is about is this one

http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

I think you can simply add extra records with the UNION, e.g. get the select you have right now in CTE and then get records from it and the rest which are not in this CTE (exceptions) and UNION ALL the results.

PluralSight Learning Library

 

[glyn6]

That link's great, I moved the main joins into the stuff statement and then added a final join outside to limit it to just the people I wanted.

I've got it down to this incase anybody else has a similar problem, it's always nice when someone puts the answer in

 SELECT distinct siteUser.siteUserID,
    (siteUser.siteUserSurname + ', ' + siteUser.siteUserForename ) as SurnameForenameTitle,

    STUFF(
    (
      SELECT ',' + DogBreedName
      FROM DogBreed
      INNER JOIN ProjBreederBreedLink p1 ON p1.siteUserID = siteUser.siteUserID
      INNER JOIN ProjBreederBreedLink p2 ON p2.DogBreedID = DogBreed.DogBreedID
      WHERE p2.DogBreedID = DogBreed.DogBreedID
      AND p1.siteUserID = siteUser.siteUserID
      FOR XML PATH('')
    ), 1, 1, '') as doglist

    FROM siteUser
    INNER JOIN ProjBreeder p3 ON p3.siteUserID = siteUser.siteUserID

Thanks for the help