DECLARE @T TABLE
(
	Name VARCHAR(50),
	Adress VARCHAR(50),
	PhoneType VARCHAR(50),
	PhoneNumber VARCHAR(50)
)

DECLARE @CARS TABLE
(
DriverName VARCHAR(50),
Car VARCHAR(50),
CarType VARCHAR(50)  
)

INSERT INTO @T
SELECT 'Petya','Moscow','Home','25-9-21'
UNION ALL
SELECT 'Petya','Moscow','Mobile','8985-349-00-XX'
UNION ALL
SELECT 'Vasya','Vladivostok','Mobile','8929-585-34-XX'


INSERT INTO @CARS
SELECT 'Petya','Honda','Sedan'
UNION ALL
SELECT 'Vasya', 'Toyota','Hatchback'


SELECT * FROM @T
SELECT * FROM @CARS


SELECT DISTINCT
t1.Name,
t1.Adress,
Cars.Car,
Cars.CarType,
PhoneNumbers.PhoneType,
PhoneNumbers.PhoneNumber
from
@T t1
INNER JOIN @T PhoneNumbers ON t1.Name = PhoneNumbers.Name
INNER JOIN @CARS Cars ON t1.Name = Cars.DriverName
FOR JSON AUTO

SELECT DISTINCT
t1.Name AS [Person.Name],
t1.Adress AS [Person.Adress],
(
	SELECT 
	t2.PhoneType,
	t2.PhoneNumber
	FROM  @T as t2 
	WHERE t2.Name = t1.Name
	FOR JSON PATH
) as [phones],
(
	SELECT 
	t3.Car,
	t3.CarType
	FROM  @CARS as t3 
	WHERE t3.DriverName = t1.Name
	FOR JSON PATH
) as [cars]
FROM
@T t1
FOR JSON PATH