one to many relation between table columns. Grouping and finding combinations

A "self-join" may be used but ensuring that one of the product ids is greater then than the other so that we get get "pairs" of products per order. Then it is simple to count:

Demo

CREATE TABLE OrderDetail
    ([OrderID] int, [ProductID] int)
;

INSERT INTO OrderDetail
    ([OrderID], [ProductID])
VALUES
    (0001, 1254), (0001, 1252), (0002, 0038), (0003, 1254), (0003, 1252), (0003, 1432), (0004, 0038), (0004, 1254), (0004, 1252)
;

Query 1:

select -- top(2)
      od1.ProductID, od2.ProductID, count(*) count_of
from OrderDetail od1
inner join OrderDetail od2 on od1.OrderID = od2.OrderID and od2.ProductID > od1.ProductID
group by
      od1.ProductID, od2.ProductID
order by
      count_of DESC

Results:

| ProductID | ProductID | count_of |
|-----------|-----------|----------|
|      1252 |      1254 |        3 |
|      1252 |      1432 |        1 |
|      1254 |      1432 |        1 |
|        38 |      1252 |        1 |
|        38 |      1254 |        1 |

----

With respect to displaying the "top 2" or whatever. You are likely to get "equal top" results so I would suggest you need to use dense_rank() and you may even want to "unpivot" the result so you have a single column of productids with their associated rank. How often you perform this and/or store this I leave to you.

with ProductPairs as (
      select 
             p1, p2, count_pair
          , dense_rank() over(order by count_pair DESC) as ranked
      from (
            select
                  od1.ProductID p1, od2.ProductID p2, count(*) count_pair
            from OrderDetail od1
            inner join OrderDetail od2 on od1.OrderID = od2.OrderID and od2.ProductID > od1.ProductID
            group by
                  od1.ProductID, od2.ProductID
            ) d
      )
, RankedProducts as (
       select p1 as ProductID, ranked, count_pair
       from ProductPairs
       union all
       select p2 as ProductID, ranked, count_pair
       from ProductPairs
       )
select *
from RankedProducts
where ranked <= 2
order by ranked, ProductID

  WITH products as (
       SELECT DISTINCT ProductID
       FROM orders
  ),  permutation as (
      SELECT p1.ProductID as pidA, 
             p2.ProductID as pidB
      FROM products p1
      JOIN products p2
        ON p1.ProductID < p2.ProductID
  ), check_frequency as (
      SELECT pidA, pidB, COUNT (o2.orderID) total_orders
      FROM permutations p
      LEFT JOIN orders o1
        ON p.pidA = o1.ProductID
      LEFT JOIN orders o2
        ON p.pidB = o2.ProductID
       AND o1.orderID = o2.orderID
      GROUP BY pidA, pidB
  )
  SELECT TOP 2 *
  FROM check_frequency
  ORDER BY total_orders DESC

The following query calculates the number of two-way combinations among all orders in Orderline:

SELECT SUM(numprods * (numprods - 1)/2) as numcombo2 
FROM ( SELECT orderid, COUNT(DISTINCT productid) as numprods
      FROM orderline ol 
      GROUP BY orderid ) o

Notice that this query counts distinct products rather than order lines, so orders with the same product on multiple lines do not affect the count. The number of two-way combinations is 185,791. This is useful because the number of combinations pretty much determines how quickly the query generating them runs. A single order with a large number of products can seriously degrade performance. For instance, if one order contains a thousand products, there would be about five hundred thousand two-way combinations in just that one order—versus 185,791 in all the orders data. As the number of products in the largest order increases, the number of combinations increases much faster.subject to the conditions:

• The two products in the pair are different
• No two combinations have the same two products.

The approach for calculating the combinations is to do a self-join on the Orderline table, with duplicate product pairs removed. The goal is to get all pairs of products The first condition is easily met by filtering out any pairs where the two products are equal. The second condition is also easily met, by requiring that the first product id be smaller than the second product id. The following query generates all the combinations in a subquery and counts the number of orders containing each one:

SELECT p1, p2, COUNT(*) as numorders
FROM (SELECT op1.orderid, op1.productid as p1, op2.productid as p2
FROM (SELECT DISTINCT orderid, productid FROM orderline) op1 JOIN
(SELECT DISTINCT orderid, productid FROM orderline) op2
ON op1.orderid = op2.orderid AND
op1.productid < op2.productid
) combinations
GROUP BY p1, p2

source Data Analysis Using SQL and Excel

Try using the following commnand:

SELECT T1.orderID,T1.productId,T2.productID,Count(*) as Occurence
FROM TBL T1 INNER JOIN TBL T2
ON T1.orderid = T2.orderid
WHERE t1.productid > T2.productId
GROUP BY T1.orderID,T1.productId,T2.productID
ORDER BY Occurence DESC

SQL fiddle