Click here to Skip to main content
14,386,394 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
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.

1 solution

Rate this:
Please Sign up or sign in to vote.

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
   
Comments
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)




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