How to return 1 row when searching for a key in multiple tables and returning a concatenated result in a column

Question

Context:

• I have a list of stores where I need to use the store's name to match against any combination of the franchise, north_region or south_region as they are named differently depending on the region or whether you're a store or franchise. I need to match the names so we can find the right franchise
• I need the correct Franchise to find the opening franchise_location information

stores table

id	name	franchise_id
1	Mc Donalds	1
2	KFC	2
3	Burgers & Kings	3
4	Dominos	4
5	Pizzzzzzzzzza Hutter	5

franchises table

id	name
1	Mc Donalds
2	Kentucky
3	Burger King
4	Dominos Pizza
5	Pizza Hut

northern_region table

id	name	franchise_id
1	KFC	2
2	Burgers & Kings	3
3	Pizzzzzzzzzza Hutter	5
4	Pizzzzzzzzzza Hutter	5
5	Pizzzzzzzzzza Hutter	5

southern_region table

id	name	franchise_id
1	Burgers & Kings	3
2	Dominos	4
3	Pizzzzzzzzzza Hutter	5

franchise_locations

id	location	opening_date	franchise_id
1	Earth	2019-10-21	1
2	Venus	2020-10-21	1
3	Mercury	2020-07-04	2
4	Saturn	2020-09-14	3
5	Mars	2020-12-23	4
6	Jupitor	2020-02-16	4
7	Pluto	2020-04-14	5

I have the following requirements:

1. Only return unique stores (no duplicates)
2. return a concatenated list of opening_date

Here is my SQL with the current output:

( http://sqlfiddle.com/#!17/6ad91/12 )

select s.id as store_id
  , s.name as store_name
  , case
      when f.id is not null then f.id
      when nr_f.id is not null then nr_f.id
      when sr_f.id is not null then sr_f.id
    end as franchise_id
  , case
      when f.id is not null then f.name
      when nr_f.id is not null then nr_f.name
      when sr_f.id is not null then sr_f.name
    end as franchise_name
  , fl.opening_date
from stores s
  left join franchise f on lower(s.name) = lower(f.name)
  left join northern_region nr on lower(s.name) = lower(nr.name)
  left join franchise nr_f on nr.franchise_id = nr_f.id
  left join southern_region sr on lower(s.name) = lower(sr.name)
  left join franchise sr_f on sr.franchise_id = sr_f.id
  left join franchise_locations fl 
    on f.id = fl.franchise_id
    or nr_f.id = fl.franchise_id
    or sr.id = fl.franchise_id
order by s.id

store_id	store_name	franchise_id	franchise_name	opening_date
1	Mc Donalds	1	Mc Donalds	2020-10-21
1	Mc Donalds	1	Mc Donalds	2019-10-21
2	KFC	2	Kentucky	2020-07-04
3	Burgers & Kings	3	Burger King	2019-10-21
3	Burgers & Kings	3	Burger King	2020-10-21
3	Burgers & Kings	3	Burger King	2020-09-14
4	Dominos	4	Dominos Pizza	2020-07-04
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-09-14
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-04-14
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-09-14
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-04-14
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-09-14
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-04-14

Here is how I want the output to look:

store_id	store_name	franchise_id	franchise_name	opening_date
1	Mc Donalds	1	Mc Donalds	2020-10-21,2019-10-21
2	KFC	2	Kentucky	2020-07-04
3	Burgers & Kings	3	Burger King	2019-10-21,2020-09-14
4	Dominos	4	Dominos Pizza	2020-07-04
5	Pizzzzzzzzzza Hutter	5	Pizza Hut	2020-09-14,2020-04-14

Answer 1

If you convert your dates to strings you can concatenate them with the stuff() function and a group by.

See the link below for help on the stuff function and group by. You can also use a CTE to avoid writing out your query twice

How to use GROUP BY to concatenate strings in SQL Server?

couldn't test it without your tables, but this should get you most of the way

with YourQuery as (select s.id as store_id
   , s.name as store_name
   , case
     when f.id is not null then f.id
  when nr_f.id is not null then nr_f.id
  when sr_f.id is not null then sr_f.id
end as franchise_id
  , case
  when f.id is not null then f.name
  when nr_f.id is not null then nr_f.name
  when sr_f.id is not null then sr_f.name
end as franchise_name
, fl.opening_date
from stores s
left join franchise f on lower(s.name) = lower(f.name)
left join northern_region nr on lower(s.name) = lower(nr.name)
left join franchise nr_f on nr.franchise_id = nr_f.id
left join southern_region sr on lower(s.name) = lower(sr.name)
left join franchise sr_f on sr.franchise_id = sr_f.id
left join franchise_locations fl on f.id = fl.franchise_id
                                 or nr_f.id = fl.franchise_id
                                 or sr.id = fl.franchise_id
)


select store_id
     , store_name
     , franchise_id
     , franchise_name
     , STUFF((select ', ' + cast(y2.opening_date as varchar)
              from YourQuery y2
              where y1.store_id = y2.store_id
                and y1.store_name = y2.store_name
                and y1.franchise_id = y2.franchise_id
                and y1.franchise_name = y2.franchise_name
              for XML Path(''), Type)
       , 1,2,'')as Opening_Dates
from YourQuery y1
group by store_id, store_name, franchise_id, franchise_name