SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- create table
create table Computer (
  Id int Identity(1, 1) not null,
  Name varchar(100) not null,
  constraint pk_computer primary key (Id)
);

create table HardDisk (
  Id int Identity(1, 1) not null,
  Interface varchar(100) not null,
  ComputerId int not null,
  constraint pk_harddisk primary key (Id),
  constraint fk_computer_harddisk foreign key (ComputerId) references Computer(Id)
);
-- insert data
insert into Computer (Name) values ('Ace Computer');
insert into Computer (Name) values ('Ok Computer');

insert into HardDisk (Interface, ComputerId) values ('SATA', 1), ('SAS', 1);
insert into HardDisk (Interface, ComputerId) values ('SATA', 2), ('SAS', 2), ('SAS', 2), ('SAS', 2), ('SATA', 2);
9 rows affected

-- select query
select c.Id as computer_id,
       string_agg(cast(hd.Interface as nvarchar(max)), ' ') as hard_disk_interfaces
from Computer c
left join HardDisk hd on c.Id = hd.ComputerId
group by c.Id;


computer_id
hard_disk_interfaces


1
SATA SAS

2
SATA SAS SAS SAS SATA


;WITH cte AS 
(
  SELECT c.Id, Interface = CONVERT(varchar(max), hd.Interface)
  from dbo.Computer AS c
  left join dbo.HardDisk AS hd on c.Id = hd.ComputerId
  group by c.Id, hd.Interface
)
SELECT Id, STRING_AGG(Interface, ' ')
FROM cte
GROUP BY Id;


Id
(No column name)


1
SAS SATA

2
SAS SATA


SELECT c.Id, STRING_AGG(x.Interface, ' ')
FROM dbo.Computer AS c
OUTER APPLY
(
  SELECT Interface = CONVERT(varchar(max), Interface) 
    FROM dbo.HardDisk WHERE ComputerID = c.Id
    GROUP BY Interface
) AS x
GROUP BY c.Id;


Id
(No column name)


1
SAS SATA

2
SAS SATA