Two columns into One single cell SQL query

Question

Select COALESCE(
(Select Convert(nvarchar(20),Count(ID))+' '+'Adult(s) - ' From TourPerson Where BookingID = 1 And [Type] = 1),
(Select Convert(nvarchar(20),Count(ID))+' '+'Child(s)' From TourPerson Where BookingID = 1 And [Type] = 2)  
) as TotalPassengers

I want to take it in below mentioned format

1 Adult - 0 Children

now it is only giving adults

Answer 1

Use conditional aggregation:

select sum(case when [Type] = 1 then 1 else 0 end) as NumAdults,
       sum(case when [Type] = 2 then 1 else 0 end) as NumChildren
from TourPerson
where BookingId = 1;

You can do the formatting in the application layer. If you really want this as a string, I would suggest using replace():

select replace(replace('<a> Adult - <c> Children',
                       '<a>', sum(case when [Type] = 1 then 1 else 0 end)
                      ),
               '<c>', sum(case when [Type] = 2 then 1 else 0 end)
              )
from TourPerson
where BookingId = 1;

I find that it is easier to control the format of the resulting string using replace(), rather than concatenating a bunch of expressions together.