Combining data into one table cell

There is a query that returns a temporary table. TSQL Query:

SELECT 
    CAST(SOH.OrderDate AS DATE) as DateYMD,
    P.LastName,
    P.FirstName,
    PP.[Name] + ' Qty: ' + CAST(SOD.OrderQty AS varchar(10)) as [OrderContent]

FROM Person.Person as P
    JOIN Sales.Customer SC ON P.BusinessEntityID = SC.PersonID
    JOIN Sales.SalesOrderHeader as SOH ON SC.CustomerID = SOH.CustomerID
    AND SOH.SalesOrderID = (
        SELECT TOP 1 subSOH.SalesOrderID
        FROM Sales.SalesOrderHeader as subSOH
        WHERE subSOH.CustomerID = SOH.CustomerID
        ORDER BY subSOH.OrderDate DESC 
    )
    JOIN Sales.SalesOrderDetail as SOD ON SOH.SalesOrderID = SOD.SalesOrderID
    JOIN Production.Product as PP ON SOD.ProductID = PP.ProductID

Result: Random sampling of output values

Is it possible to group data from the last column, thereby reducing the number of lines in the output. That is, I need it to be like this: Desired result

UPD: SQL Server 2016. STRING_AGG is missing

Answer

Group by the values you need (DateYMD,LastName,FirstName) and STRING_AGG can do this

Select DateYMD,LastName,FirstName, STRING_AGG(OrderContent,';') order_contents from
(
SELECT 
    CAST(SOH.OrderDate AS DATE) as DateYMD,
    P.LastName,
    P.FirstName,
    PP.[Name] + ' Qty: ' + CAST(SOD.OrderQty AS varchar(10)) as [OrderContent]

FROM Person.Person as P
    JOIN Sales.Customer SC ON P.BusinessEntityID = SC.PersonID
    JOIN Sales.SalesOrderHeader as SOH ON SC.CustomerID = SOH.CustomerID
    AND SOH.SalesOrderID = (
        SELECT TOP 1 subSOH.SalesOrderID
        FROM Sales.SalesOrderHeader as subSOH
        WHERE subSOH.CustomerID = SOH.CustomerID
        ORDER BY subSOH.OrderDate DESC 
    )
    JOIN Sales.SalesOrderDetail as SOD ON SOH.SalesOrderID = SOD.SalesOrderID
    JOIN Production.Product as PP ON SOD.ProductID = PP.ProductID ) q 
    group by DateYMD,LastName,FirstName

---

STUFF FOR XML PATH for sqlserver 16 :

WITH tbl (DateYMD, LastName, FirstName ,  OrderContent)  
AS  
(  -- it is your query
SELECT 
    CAST(SOH.OrderDate AS DATE) as DateYMD,
    P.LastName,
    P.FirstName,
    PP.[Name] + ' Qty: ' + CAST(SOD.OrderQty AS varchar(10)) as [OrderContent]
FROM Person.Person as P
    JOIN Sales.Customer SC ON P.BusinessEntityID = SC.PersonID
    JOIN Sales.SalesOrderHeader as SOH ON SC.CustomerID = SOH.CustomerID
    AND SOH.SalesOrderID = (
        SELECT TOP 1 subSOH.SalesOrderID
        FROM Sales.SalesOrderHeader as subSOH
        WHERE subSOH.CustomerID = SOH.CustomerID
        ORDER BY subSOH.OrderDate DESC 
    )
    JOIN Sales.SalesOrderDetail as SOD ON SOH.SalesOrderID = SOD.SalesOrderID
    JOIN Production.Product as PP ON SOD.ProductID = PP.ProductID
)
Select distinct  DateYMD, LastName,  FirstName ,  
, STUFF((
       SELECT ',' + t1.OrderContent
         FROM tbl t1
        WHERE t1.DateYMD = t2.DateYMD and t1.LastName = t2.LastName and  t1.FirstName = t2.FirstName 
        ORDER BY t1.OrderContent
          FOR XML PATH('')), 1, LEN(','), '') AS OrderContents
            from tbl t2