TSQL Stuff with union

Question

EDIT

Sorry, I have not made a very good job of explaining myself.

The reason I am using a UNION is due to pulling this data from a form in the database. That form is made up of multiple different questions and answers, so depending on the question - I need to join to the same tables, but using different links.

So below is a better example

   select  ra.AgreementId, sub.Name

from RentAgreement ra

left join
(
--Dogs
Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(u.Description,' (Dog)') 'Name'
from  AllocationApplication aa
inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (16)
inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId in (119) /*DogBreed*/
inner join SystemFormQuestionSetup qs on qs.FormQuestionSetupId = q.FormQuestionSetupId
inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
inner join SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
where aa.ApplicationTypeId = 1 


union all

--Cats
Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(d.TextResponse,' (Cat)') 'Name'
from  AllocationApplication aa
inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (17)
inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId = 132 /*CatName*/
inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
inner join  SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
where aa.ApplicationTypeId = 1 
) sub on sub.AgreementId = ra.AgreementId

where ra.AgreementId = 1775

What that does is pull me back the dogs and cats in a property. The result set looks like below.

So this particular Agreementid - has 2 dogs (the first two row pulled from the first UNION and 1 cat. Depending on the type of pet - for a dog they want the breed to be returned - for a cat, just the name of the animal.

What I want to achieve from the code below is just two columns and 1 row. Column 1 would be the AgreementId and the second column would be name, but with all three pets in this example on one comma separated line like - Lhasa Apso (Dog), Pug (Dog), PussPuss (Cat)

Thank you very much

Answer 1

Cracked it. Thanks guys and sorry for not making myself clearer in the first place.

select                              ra.AgreementId,

                                    STUFF(( select  ISNULL(', ' + sub.Name,'')
                                    FROM    RentAgreement ra2
                                        left join
                                    (
                                    --Dogs
                                    Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(u.Description,' (Dog)') 'Name'
                                    from  AllocationApplication aa
                                    inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
                                    inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
                                    inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
                                    inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
                                    inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
                                    Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (16)
                                    inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId in (119) /*DogBreed*/
                                    inner join SystemFormQuestionSetup qs on qs.FormQuestionSetupId = q.FormQuestionSetupId
                                    inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
                                    inner join SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
                                    inner join UDFLookup u ON u.LookupReference = d.LookupId and u.LookupTypeId = 12
                                    where aa.ApplicationTypeId = 1 


                                    union all

                                    --Cats
                                    Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(d.TextResponse,' (Cat)') 'Name'
                                    from  AllocationApplication aa
                                    inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
                                    inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
                                    inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
                                    inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
                                    inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
                                    Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (17)
                                    inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId = 132 /*CatName*/
                                    inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
                                    inner join  SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
                                    where aa.ApplicationTypeId = 1 
                                    ) sub on sub.AgreementId = ra.AgreementId
                                    where ra2.AgreementId = ra.AgreementId
                                    FOR XML PATH ('')),1,2,'')  as 'PetName'


from RentAgreement ra
where ra.AgreementId = 1775

Answer 2

Based on the query we have, it seems like the entire thing could be abbreviated to the following:

SELECT e.AgreementId,
       d.TextResponse + '(Dog), ' + d.TextResponse + 'Dog(2)' AS [name]
FROM AllocationApplication aa
     INNER JOIN ContactGroupLink l ON l.ContactGroupId = aa.ContactGroupId
                                  AND l.EffectiveToDate IS NULL
     INNER JOIN RentAgreementEpisode e ON e.AgreementEpisodeId = l.LinkId
                                      AND l.LinkTypeId = 2
     INNER JOIN SystemFormResponse sfr ON sfr.FormId = aa.ApplicationId
                                      AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
     INNER JOIN SystemFormPage p ON p.FormTemplateId = sfr.FormTemplateId
                                AND p.FormPageId = 6 /*(Pet Register Page)*/
     INNER JOIN SystemFormSection s ON s.FormPageId = p.FormPageId
                                   AND s.FormSectionId = 6 /*Pet Details */
     INNER JOIN SystemFormGroup g ON g.FormSectionId = s.FormSectionId
                                 AND FormGroupId IN (16, 17, 18, 19, 20, 25, 26)
     INNER JOIN SystemFormQuestion q ON q.FormGroupId = g.FormGroupId
                                    AND q.EffectiveToDate IS NULL
                                    AND q.FormQuestionId = 118 /*DogName*/
     INNER JOIN SystemFormResponseAnswer a ON a.FormQuestionId = q.FormQuestionId
                                          AND a.FormResponseId = sfr.FormResponseId
     INNER JOIN SystemFormResponseDetail d ON d.FormResponseAnswerId = a.FormResponseAnswerId
WHERE aa.ApplicationTypeId = 1
  AND e.AgreementId = 1775;

There's no need for a UNION ALL or STUFF. Unless we're missing (a lot) of information.