MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sites as
with sites(id) as (
  select 1 union all
  select 2 union all
  select 3 union all
  select 4
)
select *
from sites

Records: 4  Duplicates: 0  Warnings: 0


create table buildings as 
with buildings(id, site_id) as (
  select 1, 1 union all
  select 2, 1 union all
  select 3, 1 union all
  select 4, 2 union all
  select 5, 2 union all
  select 6, 2 union all
  select 7, 3 union all
  select 8, 3 union all
  select 9, 3 union all
  select 10, 4
)
select *
from buildings

Records: 10  Duplicates: 0  Warnings: 0


create table levels as
with levels(id, building_id) as (
  select 1, 1 union all
  select 2, 1 union all
  select 3, 2 union all
  select 4, 2 union all
  select 5, 2 union all
  select 6, 3 union all
  select 7, 4 union all
  select 8, 4 union all
  select 9, 5 union all
  select 10, 6 union all
  select 11, 6 union all
  select 12, 7 union all
  select 13, 7 union all
  select 14, 7 union all
  select 15, 8 union all
  select 16, 8 union all
  select 17, 9
)
select *
from levels

Records: 17  Duplicates: 0  Warnings: 0


with b_l as (
  select
    b.id as building_id
    , b.site_id
    , concat('('
      , group_concat(
          l.id
          order by l.id
          separator ','
        )
      , ')'
    ) as building_levels
  from buildings as b
    left join levels as l
      on b.id = l.building_id
  group by b.id, b.site_id
)
, s_b as (
  select
    s.id
    , group_concat(
        b_l.building_id
        order by b_l.building_id
        separator ','
      ) as buildings
    , group_concat(
        b_l.building_levels
        order by b_l.building_id
        separator ','
      ) as levels
  from sites as s
    left join b_l
      on s.id = b_l.site_id
  group by s.id
)
select *
from s_b


id
buildings
levels


1
1,2,3
(1,2),(3,4,5),(6)

2
4,5,6
(7,8),(9),(10,11)

3
7,8,9
(12,13,14),(15,16),(17)

4
10
null


select
  s.id
  , group_concat(
      b_l.building_id
      order by b_l.building_id
      separator ','
    ) as buildings
  , group_concat(
      b_l.building_levels
      order by b_l.building_id
      separator ','
    ) as levels
from sites as s
  left join lateral (
    select
      b.site_id
      , b.id as building_id
      , concat('('
          , group_concat(
              l.id
              order by l.id
              separator ','
            )
          , ')'
        ) as building_levels
    from buildings as b
      left join levels as l
        on b.id = l.building_id
    group by b.id, b.site_id
  ) b_l
    on s.id = b_l.site_id
group by s.id


id
buildings
levels


1
1,2,3
(1,2),(3,4,5),(6)

2
4,5,6
(7,8),(9),(10,11)

3
7,8,9
(12,13,14),(15,16),(17)

4
10
null
id	buildings	levels
1	1,2,3	(1,2),(3,4,5),(6)
2	4,5,6	(7,8),(9),(10,11)
3	7,8,9	(12,13,14),(15,16),(17)
4	10	null