Unique coverage between a pair of values in SQL-CodePudding

I want to get a distinct list of Item_IDs per vendor pair, e.g. (A,B), (A,C), (B,C) I have a table like this:

Item_ID | vendor 
123         A 
123         B 
133         B 
456         C

I want to list ALL of the distinct Item_IDs between each pair of vendors, like below. Essentially, I want to know all of the Item_IDs which are "covered" (or present) between a pair of vendors.

Vendor1 | Vendor 2 | Item_ID
A           B         123
A           B         133 
A           C         123 
A           C         456
B           C         123
B           C         133 
B           C         456

I have tried using a self join, to get 2 vendors side by side, but struggling on how to list out the Item_IDs per pair. Here is what I have so far:

select 
  distinct a.vendor as vendor1, b.vendor as vendor2
from table a 
join table b on a.vendor != b.vendor 
            and a.vendor >= b.vendor -- to remove duplicate combinations

CodePudding user response：

The following query works in SQL Server 2019 but I don't have the chance of testing it in Azure.

You can do:

with
v as (select distinct vendor as vendor from t)
select 
  a.vendor as vendor1,
  b.vendor as vendor2,
  x.item_id
from v a
join v b on b.vendor > a.vendor
cross apply (
  select distinct item_id 
  from t 
  where t.vendor = a.vendor or t.vendor = b.vendor
) x
order by a.vendor, b.vendor

Result:

 vendor1  vendor2  item_id 
 -------- -------- ------- 
 A        B        123     
 A        B        133     
 A        C        123     
 A        C        456     
 B        C        123     
 B        C        133     
 B        C        456

See running example at db<>fiddle.