Combining data into one table cell-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You say you only have SQL Server 2016. So you will have to use the FOR XML aggregation method

SELECT 
    CAST(SOH.OrderDate AS DATE) as DateYMD,
    P.LastName,
    P.FirstName,

    STUFF((
        SELECT CONCAT(',', PP.[Name], ' Qty: ', SOD.OrderQty)
        FROM Sales.SalesOrderDetail as SOD
        JOIN Production.Product as PP ON SOD.ProductID = PP.ProductID
        WHERE SOH.SalesOrderID = SOD.SalesOrderID
        FOR XML PATH(''), TYPE
      ).value('text()[1]','nvarchar(max)'),
      1, LEN(','), '') as [OrderContent]

FROM Person.Person as P
    JOIN Sales.Customer SC ON P.BusinessEntityID = SC.PersonID
    OUTER APPLY (
        SELECT TOP 1 subSOH.*
        FROM Sales.SalesOrderHeader as subSOH
        WHERE subSOH.CustomerID = SOH.CustomerID
        ORDER BY subSOH.OrderDate DESC 
    ) SOH;

Note the use of OUTER APPLY to get the top SalesOrderHeader per CustomerID. You could also use ROW_NUMBER() for this.

CodePudding user response：

Thank you very much @Charlieface for demonstrating this solution. I nevertheless made the request a little in my own way, because an additional condition was to make a selection of the very first order for each customer. The final solution to my problem:

SELECT 
    CAST(SOH.OrderDate AS DATE) as DateYMD,
    P.LastName,
    P.FirstName,
STUFF((
        SELECT CONCAT(',', PP.[Name], ' Qty: ', SOD.OrderQty)
        FROM Sales.SalesOrderDetail as SOD
        JOIN Production.Product as PP ON SOD.ProductID = PP.ProductID
        WHERE SOH.SalesOrderID = SOD.SalesOrderID
        FOR XML PATH(''), TYPE
      ).value('text()[1]','nvarchar(max)'),
      1, LEN(','), '') 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 
    )
ORDER BY LastName ASC