I used this data
CREATE TABLE #ITEMSOFSTORES
(
ITEM INT,
STORE INT
)
INSERT INTO #ITEMSOFSTORES (ITEM,STORE) VALUES (1,1),(1,3),(4,1),(4,3),(5,1),(5,3),(2,1),(2,2),(2,4),(3,1),(3,2),(3,4);
And I created another temporary table
CREATE TABLE #groups (ITEM int, STORE int, stores varchar(125))
Which I then populated with this query:
INSERT INTO #groups (item, store, stores)
SELECT ITEM, STORE, STUFF
(
(
SELECT ',' + CAST(STORE AS VARCHAR)
FROM #ITEMSOFSTORES B
WHERE A.ITEM = B.ITEM
ORDER BY STORE
FOR XML PATH('')
), 1, 1, ''
) AS stores
FROM #ITEMSOFSTORES A
This essentially lists all of the items with a comma separated list of the stores in which it can be found. The query
select DISTINCT ITEM, STORES from #groups
gives these results:
ITEM STORES
1 1,3
2 1,2,4
3 1,2,4
4 1,3
5 1,3
and
select ROW_NUMBER() OVER (ORDER BY STORES), STORES from #groups GROUP BY STORES
will give each of those "groupings" a number so you can combine them to get your results
select DISTINCT ITEM, N from #groups A
INNER JOIN
(select ROW_NUMBER() OVER (ORDER BY STORES) AS N, STORES from #groups GROUP BY STORES) B ON A.STORES = B.STORES