14,386,394 members
Rate this:
See more:
```WITH CTE AS
(
SELECT FETCHNEXT.*
, RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID)
FROM FETCHNEXT
)
SELECT *
FROM CTE
WHERE RN = 1
UNION ALL

SELECT *
FROM CTE
WHERE RN = 2
UNION ALL

SELECT *
FROM CTE
WHERE RN = 3```

What I have tried:

```WITH CTE AS
(
SELECT FETCHNEXT.*
, RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID)
FROM FETCHNEXT
)
SELECT *
FROM CTE
WHERE RN = 1
UNION ALL

SELECT *
FROM CTE
WHERE RN = 2
UNION ALL

SELECT *
FROM CTE
WHERE RN = 3```
Posted
Updated 8-Apr-19 9:08am
v9
ZurdoDev 20-Feb-19 8:49am

I don't quite follow but it doesn't sound too hard. It sounds like you want to group stores together by the fact that they share items? So, just group by store.

Rate this:

## Solution 1

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```
Member 14156756 21-Feb-19 4:02am

```OUTPUT NEEDED:-

STOREGROUS TABLE
GroupId Strore
1       1
1       3
2       1
2       2
2       4

ITEMGROUPS TABLE
GroupId Item
1       1
1       4
1       5
2       3
2       2```
CHill60 21-Feb-19 5:13am

I've done all the hard work! That last query effectively creates the ITEMGROUPS table. Just do something similar for the STOREGROUPS table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Richard MacCutchan 220 OriginalGriff 185 CPallini 140 Maciej Los 100 RickZeeland 48
 OriginalGriff 1,465 Richard Deeming 825 RickZeeland 788 Richard MacCutchan 558 Maciej Los 415

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100