Author |
Topic |
sushmanem
Starting Member
2 Posts |
Posted - 2013-10-27 : 20:20:56
|
I have 2 tables - Orders and Order_detailsOrders table has customer_id and order_idOrder_details has order_id and product_id.I want to list all customers that have ordered same product through different orders. SOmething like this -customer_id Product_id Order_id1 10 100,1102 10 121,1313 20 115,119 Query I have only lists the number of orders but not the actual orders. In Oracle, group_concat function lists out the orders but cant get it in sql. Can anyone pleaseee help me?I tried using stuff function but then it is asking me to include the order_id column in group function which then does not result any dataSELECT d.customer_id ,d.product_id , COUNT(DISTINCT d.orderid) repeated_orders --GROUP_CONCAT(DISTINCT d.orderid) --stuff((select ','+ orderid from SolPot.dbo.TblRequests where orderid=d.orderid FOR XML PATH('')) , 1 , 1,'') FROM ( SELECT c.customer_id ,o.product_id, o.orderid FROM orders c JOIN order_details o ON (c.orderid= o.orderid) ) d GROUP BY d.customer_id ,d.product_idHAVING COUNT(DISTINCT d.orderid) > 1; |
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-10-28 : 02:53:24
|
DECLARE @ORDERS TABLE(CUSTID INT, ORDERID INT)INSERT INTO @ORDERS VALUES (1,100),(1,110),(2,121),(2, 131),(3,115),(3,119)DECLARE @ORDER_DETAILS TABLE(ORDERID INT, PRODUCTID INT)INSERT INTO @ORDER_DETAILS VALUES (100,10),(110,10),(121,10),(131,10),(115,20),(119,20)--SELECT * FROM @ORDERS--SELECT * FROM @ORDER_DETAILS;WITH CTEAS(SELECT O.CUSTID, D.PRODUCTID, O.ORDERIDFROM @ORDERS O INNER JOIN @ORDER_DETAILS D ON O.ORDERID = D.ORDERID)SELECT DISTINCT CUSTID, PRODUCTID, STUFF ( (SELECT ',' + CAST(ORDERID AS VARCHAR) FROM CTE WHERE CUSTID=C.CUSTID FOR XML PATH('')), 1, 1, '') FROM CTE C--------------------Rock n Roll with SQL |
|
|
sushmanem
Starting Member
2 Posts |
Posted - 2013-10-28 : 05:56:33
|
Thank you very much.. Much appreciated.quote: Originally posted by rocknpop DECLARE @ORDERS TABLE(CUSTID INT, ORDERID INT)INSERT INTO @ORDERS VALUES (1,100),(1,110),(2,121),(2, 131),(3,115),(3,119)DECLARE @ORDER_DETAILS TABLE(ORDERID INT, PRODUCTID INT)INSERT INTO @ORDER_DETAILS VALUES (100,10),(110,10),(121,10),(131,10),(115,20),(119,20)--SELECT * FROM @ORDERS--SELECT * FROM @ORDER_DETAILS;WITH CTEAS(SELECT O.CUSTID, D.PRODUCTID, O.ORDERIDFROM @ORDERS O INNER JOIN @ORDER_DETAILS D ON O.ORDERID = D.ORDERID)SELECT DISTINCT CUSTID, PRODUCTID, STUFF ( (SELECT ',' + CAST(ORDERID AS VARCHAR) FROM CTE WHERE CUSTID=C.CUSTID FOR XML PATH('')), 1, 1, '') FROM CTE C--------------------Rock n Roll with SQL
|
|
|
|
|
|