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

Possible duplicate of [get a comma delimited string from rows](https://stackoverflow.com/questions/41787834/get-a-comma-delimited-string-from-rows) — Zohar Peled, Sep 04 '19 at 13:14
Why have you got a `UNION ALL` at all? Those 2 queries look identical to each other. Am I missing something here? — Thom A, Sep 04 '19 at 13:15
@Larnu as far as I see, `CONCAT(d.TextResponse,' (Dog)') 'Name'` vs `CONCAT(d.TextResponse,' (Dog2)') 'Name'` — Zohar Peled, Sep 04 '19 at 13:16
What I thought too, @ZoharPeled; so yeah, no need for `STUFF` or `UNION ALL`. The whole thing could just be achieved with `d.TextResponse + '(Dog), ' + d.TextResponse + 'Dog(2)' AS [name]` — Thom A, Sep 04 '19 at 13:21

score 1 · Answer 1 · answered Sep 04 '19 at 13:20

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.

Assuming that all these joins doesn't produce duplicate records from `d`, I think you're answer is correct. — Zohar Peled, Sep 04 '19 at 13:25
Even if it does, the results would be the same as the OP's, @ZoharPeled, as the OP was using a `UNION ALL`, not a `UNION`, and no `DISTINCT` clause. Nothing in the out query was reducing the row count. — Thom A, Sep 04 '19 at 13:46
But what if for `AgreementId` you get two different values of `TextResponse`? then your query will return two rows, one for each value being duplicated as (dog) and (dog2), where as a query with string aggregation would return one row with 4 dog names... — Zohar Peled, Sep 04 '19 at 13:49
I don't deny that, @ZoharPeled, but the OP's SQL above would do that too; and they've said it returns the correct results (apart from that they want it delimited). The only reason the OP *appears* to have 2 rows is because they `UNION ALL` the query with itself. So it seems there no reason to have a `UNION ALL` and just write it out manually. of course, this could well be that the OP hasn't defined their question well, or their sample SQL and data; but we can only work with what we have. :) — Thom A, Sep 04 '19 at 13:51
As I wrote in my first comment, I think your answer is correct, under the same assumptions. That's why I've upvoted it. — Zohar Peled, Sep 04 '19 at 13:53

score 0 · Accepted Answer · answered Sep 06 '19 at 09:04

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

TSQL Stuff with union

2 Answers2