Subquery with every value in group-by

Question

I'm having problem selecting multiple values generated by a GROUP BY clause. I try to set up a simplified example of what I have:

Table CUSTOMERS    Table PRODUCTS            Table ORDERS
ID | NAME          ID | DESCR | PROMO         ID_P | ID_C
---+---------      ---+-------+-------        -----+-----
 1 | Alice          1 | prod1 | gold           1   | 1
 2 | Bob            2 | prod2 | gold           2   | 3
 3 | Charlie        3 | prod3 | silver         1   | 2
                    4 | prod4 | silver         3   | 1

From this I'd lik to join every product and every customer in a single cell

Results
PROMO  | products     | CUSTOMERS
-------+--------------+--------------------
gold   | prod1, prod2 | Alice, Bob, Charlie
silver | prod3        | Alice

Something like:

SELECT PRODUCTS.PROMO
     , CONCAT(PRODUCTS.DESCR)
     , STUFF(
        (SELECT ' / ' + CUSTOMERS.NAME
          FROM CUSTOMERS
          WHERE CUSTOMERS.ID = ORDERS.ID
          FOR XML PATH (''))
         , 1, 3, '')   
FROM       PRODUCTS
INNER JOIN ORDERS
 ON PRODUCTS.ID = ORDERS.ID_P
WHERE PRODUCTS.ID < 3
GROUP BY PRODUCTS.PROMO

Can this be achieved in SQL?

https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — jarlh, Dec 18 '17 at 10:24
@jarlh: thanks, I started with that post, but I get that CUSTOMER.NAME is invalid in the select list because it is not contained in GROUP BY, but I don't want to group by customer or by product — dg3, Dec 18 '17 at 10:33

score 1 · Accepted Answer · answered Dec 18 '17 at 10:35

You can use this.

DECLARE @CUSTOMERS Table(ID INT, NAME VARCHAR(20))
INSERT INTO @CUSTOMERS VALUES
( 1 ,'Alice'),             
( 2 ,'Bob'),             
( 3 ,'Charlie')             

DECLARE @PRODUCTS TABLE (ID INT, DESCR VARCHAR(10), PROMO  VARCHAR(10))
INSERT INTO @PRODUCTS VALUES
( 1 ,'prod1','gold'), 
( 2 ,'prod2','gold'), 
( 3 ,'prod3','silver'), 
( 4 ,'prod4','silver') 

DECLARE @ORDERS TABLE (  ID_P INT, ID_C INT)
INSERT INTO @ORDERS VALUES
(   1 ,1 ),
(   2 ,3 ),
(   1 ,2 ),
(   3 ,1 )


;WITH CTE AS (
SELECT O.*, P.PROMO, P.DESCR,  C.NAME  FROM @ORDERS O
INNER JOIN @PRODUCTS P ON O.ID_P = P.ID
INNER JOIN @CUSTOMERS C ON O.ID_C = C.ID
)
SELECT DISTINCT T.PROMO, 
    STUFF(Product.Descrs,1,1,'') products, 
    STUFF(Customer.Names,1,1,'') CUSTOMERS 
FROM CTE T
    CROSS APPLY (SELECT DISTINCT ',' + T1.DESCR FROM CTE T1 WHERE T.PROMO = T1.PROMO FOR XML PATH('')) AS Product(Descrs)
    CROSS APPLY (SELECT DISTINCT ',' + T1.NAME FROM CTE T1 WHERE T.PROMO = T1.PROMO FOR XML PATH('')) AS Customer(Names)

Result:

PROMO      products       CUSTOMERS                
---------- -------------- -------------------------
gold       prod1,prod2    Alice,Bob,Charlie
silver     prod3          Alice

Subquery with every value in group-by

1 Answers1