CODEWITHSUNDEEP
sqlsql-server
Budyn
Budyn

Reputation: 573

SQL add STUFF function into this query

I have the following query 

SELECT TOP 1000 
    o.BuyerEMail,
    COUNT(*) HowMany,
    o.Name, o2.OrderID
FROM 
    Orders o
JOIN 
    (SELECT 
         BuyerEmail, MAX(OrderDate) Latest 
     FROM Orders 
     GROUP BY BuyerEmail) l ON o.BuyerEmail = l.BuyerEmail
JOIN 
    Orders o2 ON l.BuyerEmail = o2.BuyerEmail
              AND l.OrderDate = o2.OrderDate
WHERE 
    Pay != 'PayPal'
GROUP BY 
    o.BuyerEmail, o.Name, l.Latest
ORDER BY

It's just producing a report about customers and how many orders they have with us and the latest order.

What I want to add is a list of the products of their last order which are in lines table.

Lines table is linked to order table by OrderID.

I was looking for something like:

JOIN
    (SELECT 
         OrderID,
         STUFF((SELECT ', ' + li.Code
                FROM tblLines li
                WHERE li.OrderID = o2.OrderID 
                FOR XML PATH ('')), 1, 1, '') AS [Codes]
GROUP BY
    OrderID, Code

So the final table displays as

BuyerEmail | HowMany | Name | Latest | Codes
---------------------------------------------
Bob@bob    | 4       | bob  | 10000  | 123,10,201231

Upvotes: 4

Views: 561

Answers (2)

Sanjay
Sanjay

Reputation: 342

SELECT TOP 1000 
o.BuyerEMail,
COUNT(*) HowMany,
o.Name, l.Latest,
STUFF((SELECT ', ' + li.Code
            FROM tblLines li
            WHERE li.OrderID = o2.OrderID 
            FOR XML PATH ('')), 1, 1, '') AS [Codes]

FROM Orders o JOIN (SELECT BuyerEmail, MAX(OrderDate) Latest FROM Orders GROUP BY BuyerEmail) l ON o.BuyerEmail = l.BuyerEmail JOIN Orders o2 ON l.BuyerEmail = o2.BuyerEmail AND l.OrderDate = o2.OrderDate WHERE Pay != 'PayPal' GROUP BY o.BuyerEmail, o.Name, l.Latest ORDER BY

Upvotes: 0

Raul
Raul

Reputation: 3121

As you posted the query from another question (SQL most recent order? MS SQL), I will use my answer as it is cleaner than the above query: 

SELECT o.*
, OrderID as LastOrderID
FROM (
    SELECT BuyerEMail
    , Name
    , COUNT(*) as TotalOrders
    FROM Orders
    WHERE Pay != 'PayPal'
    GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders s
    WHERE s.BuyerEmail = o.BuyerEmail
    ORDER BY OrderDate DESC
) ca

You posted a good example, it wasn't complete thou. You would need the following xmlpath query:

SELECT OrderID
, Codes
FROM tblLines r1
CROSS APPLY ( 
    SELECT
        STUFF((SELECT ',' + CAST(Code AS NVARCHAR)
        FROM tblLines r2
        WHERE r2.OrderID = r1.OrderID
        GROUP BY OrderID, Code
        ORDER BY Code
        FOR XML PATH (''), TYPE)
        .value('.', 'varchar(max)')
        , 1, 1, ''))  OrderLines(Codes)
GROUP BY OrderID, OrderList

Add it to the previous statement with a simple join:

SELECT  o.BuyerEMail
    ,o.Name
    ,o.TotalOrders
    , OrderID as LastOrderID
    , c.Codes
FROM (
        SELECT BuyerEMail
        , Name
        , COUNT(*) as TotalOrders
        FROM Orders
        WHERE Pay != 'PayPal'
        GROUP BY BuyerEmail, Name
) o
CROSS APPLY (
        SELECT TOP 1 OrderID, OrderDate
        FROM Orders s
        WHERE s.BuyerEmail = o.BuyerEmail
        ORDER BY OrderDate DESC
) ca 
INNER JOIN (
    SELECT OrderID
    , Codes
    FROM tblLines r1
    CROSS APPLY ( 
        SELECT
            STUFF((SELECT ',' + CAST(Code AS NVARCHAR)
            FROM tblLines r2
            WHERE r2.OrderID = r1.OrderID
            GROUP BY OrderID, Code
            ORDER BY Code
            FOR XML PATH (''), TYPE)
            .value('.', 'varchar(max)')
            , 1, 1, ''))  OrderLines(Codes)
    GROUP BY OrderID, OrderList
) c
ON ca.OrderID = c.OrderID

Upvotes: 4

Related Questions