將資料合并到一個表格單元格中

有一個回傳臨時表的查詢。TSQL 查詢：

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

結果： 輸出值的隨機抽樣

是否可以對最后一列的資料進行分組，從而減少輸出中的行數。也就是說，我需要它是這樣的：期望的結果

UPD：SQL Server 2016。缺少 STRING_AGG

uj5u.com熱心網友回復：

按您需要的值 (DateYMD,LastName,FirstName)分組，STRING_AGG可以做到這一點

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

sqlserver 16 的 XML 路徑的東西：

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

uj5u.com熱心網友回復：

你說你只有 SQL Server 2016。所以你將不得不使用FOR XML聚合方法

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;

請注意使用OUTER APPLY來獲取SalesOrderHeaderper的頂部CustomerID。您也可以ROW_NUMBER()為此使用。

uj5u.com熱心網友回復：

非常感謝@Charlieface 演示此解決方案。盡管如此，我還是以自己的方式提出了一些要求，因為附加條件是為每個客戶選擇第一個訂單。我的問題的最終解決方案：

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

標籤：sql sql-server 查询语句

上一篇：“=”/like子句中存盤程序中的REGEX

下一篇：SQL查詢-根據條件顯示常見行