concatenate the rows into one with multiple left joins

Question

I have a query as below:

select 
    a.person_name ,
    f.speciality,
    a.email,
    a.phone, 
    a.address
    na.descrition 'nationality',
    ci.description 'city'
    from person a
    left join lookup na on a.Country_id = na.lookup_id and na.lookup_type='FND_COUNTRIES'
    left join lookup ci on a.City_id = ci.lookup_id and ci.lookup_type='IM_CITIES'
    left join speciality pe on a.id = pe.person_id 
    left join np_speciality f on pe.speciality_id = f.speciality_id
    , 
    person_dtl b

    where a.id = b.person_id

result of this

person_name        speciality          email        phone    nationality    city  
    a                   1                                           20          30
    a                   20                                          20          30
    a                   30                                          20          30

I need the below result:

   person_name        speciality          email        phone    nationality    city  
    a                   1,20,30                                     20          30

hOw can i achieve the same

found answers for the same but its not able to solve the issue because of multiple left join in the query

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — CannedMoose, Jun 20 '18 at 08:29

iamdave · Answer 1 · 2018-06-20T09:55:18.067

You can replicate a group_concat type function using stuff and for xml. Without sample data this is obviously untested, but should be near enough what you need:

select p.person_name
      ,stuff((select ', ' + f.speciality
              from np_speciality f
              where pe.speciality_id = f.speciality_id
              for xml path('')
              )
             ,1,2,'') as speciality
      ,p.email
      ,p.phone
      ,p.address
      ,na.descrition as nationality
      ,ci.description as city
from person as p
    join person_dtl as pd
        on p.id = pd.person_id
    left join lookup na
        on p.Country_id = na.lookup_id
            and na.lookup_type = 'FND_COUNTRIES'
    left join lookup ci
        on p.City_id = ci.lookup_id
            and ci.lookup_type = 'IM_CITIES'
    left join speciality pe
        on p.id = pe.person_id

stuff replaces a part of a string defined by a start and end character number with another defined string. In this case, starting at character 1, replace 2 characters with a blank string. This is required because of the way the for xml works:

for xml produces an xml string of your output. By specifying no path value and a ', ' delimiter we get a string of values separated by a comma and space, with a leading comma and space at the beginning, which is removed by the stuff.

I would also strongly recommend you use modern join syntax and meaningful alias in all your scripts. Mixing join conditions with comma separated tables as well as a, b, c, etc table alias just makes your code hard to read and maintain.

concatenate the rows into one with multiple left joins

1 Answers1