SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Orders(
    Orderguid varchar(255) NOT NULL,
    fileno INT NOT NULL,
   
CONSTRAINT Orderguid PRIMARY KEY (Orderguid)
);

CREATE TABLE Property(
    
    PropertyId int NOT NULL,
    Address varchar(255),
    Zip  varchar(255) ,
    State  varchar(255) ,
    Orderguid varchar(255) ,
    CONSTRAINT PropertyId PRIMARY KEY (PropertyId),
    CONSTRAINT FKP_Orderguid FOREIGN KEY (Orderguid) REFERENCES Orders(Orderguid)
);

CREATE TABLE Buyer(
    
    BuyerId int NOT NULL,
    BuyerName  varchar(255) NOT NULL,
    
    Orderguid varchar(255) ,
    CONSTRAINT BuyerId PRIMARY KEY (BuyerId),
    CONSTRAINT FKB_Orderguid FOREIGN KEY (Orderguid) REFERENCES Orders(Orderguid)
);

INSERT INTO Orders (Orderguid, fileno)
VALUES ('8C25-00F768', 100)

INSERT INTO Orders (Orderguid, fileno)
VALUES ('2C25-00D695', 200)

INSERT INTO Property (PropertyId,Address, Zip,State,Orderguid)
VALUES (1,'Street no 1', '122235','TX','8C25-00F768')

INSERT INTO Property (PropertyId,Address, Zip,State,Orderguid)
VALUES (2,'Street no 2', '122235','TX','8C25-00F768')

INSERT INTO Buyer (BuyerId, BuyerName,Orderguid)
VALUES (1,'Dennis Richard', '8C25-00F768')

INSERT INTO Buyer (BuyerId, BuyerName,Orderguid)
VALUES (2,'Levis Richard', '8C25-00F768')

;WITH CTE AS (

select o.fileno ,
stuff((SELECT ';' + pr.Address from Property pr
inner join Orders o on o.Orderguid=p.Orderguid

FOR XML PATH ('')), 1, 1, ''
) AS Address

,
stuff((SELECT ';' + b.BuyerName from Buyer b
inner join Orders o1 on o1.Orderguid=p.Orderguid

FOR XML PATH ('')), 1, 1, ''
) AS Buyers

,p.Zip
,p.State

,  ROW_NUMBER() OVER (PARTITION BY o.Orderguid ORDER BY o.Orderguid ) rn

from Orders o 
left join Property p ON p.Orderguid=o.Orderguid 
left join Buyer b ON b.Orderguid=p.Orderguid

group by o.fileno, p.Address, p.Zip, p.State,p.Orderguid,o.Orderguid )
select * from CTE
where rn=1

fileno	Address	Buyers	Zip	State	rn
200	null	null	null	null	1
100	Street no 1;Street no 2	Dennis Richard;Levis Richard	122235	TX	1