if object_id('tempdb..##tmpA') is not null  
   drop table ##tmpA  
 Create table ##tmpA (  
 Transaction_id varchar(10),Product_id varchar(10))  
 insert into ##tmpA  
 select '1','A'  
 union all  
 select '1','B'  
 union all  
 select '2','A'  
 union all  
 select '2','B'  
 union all  
 select '2','C'  
 union all  
 select '3','A'  
 union all  
 select '3','B'  
 union all  
 select '3','D'  
 union all  
 select '4','B'  
 union all  
 select '4','C'  
  
 select a.Product_id + b.Product_id as 产品组合,count(1) as 交易单数  
 from ##tmpA a, ##tmpA b 
 where a.Transaction_id = b.Transaction_id 
 and a.Product_id <b.Product_id 
 group by a.Product_id + b.Product_id  
 order by count(1) desc 
  
 --结果 
 产品组合         交易单数 
 -------------------- ----------- 
 AB           3 
 BC           2 
 BD           1 
 AC           1 
 AD           1 
  
 (5 行受影响)

;with t as 
(
  select rn=row_number()over(order by Transaction_id),*
  from ##tmpA
)
select  a.Product_id+b.Product_id Product_id,count(1) as cnt
from  t a 
    join t b 
        on a.rn<b.rn 
        and a.Transaction_id=b.Transaction_id
group by a.Product_id+b.Product_id
order by 2 desc
/*
Product_id           cnt
-------------------- -----------
AB                   3
BC                   2
BD                   1
AC                   1
AD                   1

(5 行受影响)
*/