Solved..Assignment group by related item..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Solved..Assignment group by related item..

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-18 : 16:12:20

I have a source. Table. where there are 2 columns "Item number", which are related among themselves.
Should result group (identifi. number of related items) which belong to him.
TAB item
item1.....item2
1.........3
6.........3
8.........9
12.......13
9........15

result

itemX...group
1.........1
3.........1
6.........1
8.........2
9.........2
12........3
13........3
15........2

1 is related to 3 and 3 is related to 6
This means that these items are related and assign them some number forexample group 1 (result table)
item 1 for example. LED TV
item 3 for example. LCD TV
Item 6 is an example. plasma TV
The same with items 8,9,15 (group 2)

I know it is very complicated
There is a solution?

big thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 09:31:35

Hello Gekko,

This problem appears to require a recursive solution. Perhaps the following thread (expanding networks) will stimulate some ideas for a solution.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89323&whichpage=1

HTH.

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-19 : 10:09:37

oh jeah !

its for me very hardly query, today else I dont know use @declare etc.
thanks ehorn

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 11:26:06

There are likely many ways to accomplish this task. Here is a variation of RyanRandall's (a member here) which may stimulate some ideas for you.

--data
DECLARE @Nodes TABLE (Parent INT, Child INT)

INSERT @Nodes (Parent, Child)
SELECT '1','3'		UNION ALL
SELECT '6','3'		UNION ALL
SELECT '8','9'		UNION ALL
SELECT '12','13'	UNION ALL
SELECT '9','15'

--Add reverse relations
INSERT @Nodes SELECT Child, Parent FROM @Nodes

--setup some local vars
DECLARE @items TABLE (n INT IDENTITY(0,1), item INT)
DECLARE @n INT = 0
DECLARE @Child INT
DECLARE @Lookup TABLE (grp INT, Item INT)
INSERT @items SELECT Parent FROM @Nodes UNION SELECT Child FROM @Nodes

--begin recursion
WHILE @n <= (SELECT MAX(n) FROM @items)
BEGIN

	SELECT @Child = item FROM @items WHERE n = @n
	DECLARE @NodePaths TABLE (Path VARCHAR(8000), TopNode INT)
	INSERT @NodePaths SELECT @Child, @Child

	WHILE @@ROWCOUNT > 0
		INSERT @NodePaths
		SELECT CAST(Parent AS VARCHAR(10)) + '/' + p.Path, Parent
		FROM @NodePaths p inner join @Nodes n ON n.Child = p.TopNode
		WHERE 
			CHARINDEX('/' + CAST(Parent AS VARCHAR(10)) + '/', '/' + Path + '/') = 0 --prevent going round in circles
			and CAST(Parent AS VARCHAR(10)) + '/' + p.Path not in (select Path from @NodePaths) --prevent redoing paths already done
	SET @n+=1
END

--build resulting list
INSERT @Lookup
SELECT 
	TopNode AS grp,
	value AS item
FROM
(
	SELECT 
		d.TopNode,
		LTRIM(RTRIM(NULLIF(SUBSTRING('/' + Path + '/', Number, CHARINDEX('/', '/' + Path + '/', Number) - Number), ''))) AS value
	FROM
	(
		SELECT TopNode, MAX(Path) AS Path
		FROM @NodePaths 
		WHERE CAST(TopNode AS VARCHAR(2)) != Path
		GROUP BY TopNode
	) d, master..spt_values n
	WHERE Type = 'P' 
		AND Number <= LEN('/' + d.Path + '/')
		AND SUBSTRING('/' + d.Path + '/', Number - 1, 1) = '/'
		AND CHARINDEX('/', '/' + d.Path + '/', Number) - Number > 0		
) d

--SELECT * FROM @lookup

--***************************************************
--results for search criteria
--***************************************************

DECLARE @Item INT = 9
SELECT Item 
FROM @Lookup
WHERE grp IN (SELECT MIN(grp) FROM @lookup WHERE Item = @Item)

HTH.

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-19 : 16:19:39

Thanks, EHORN but your result is the following
grp item (order of columns does not matter)
1 1
1 6
3 1
3 3
3 6
6 1
6 3
6 6
8 8
8 9
8 15
9 8
9 9
9 15
12 12
12 13
13 12
13 13
15 8
15 15

I need a little adjustment on the following:
Result:
item grp
1 1
3 1
6 1
8 2
9 2
12 3
13 3
15 2

It is select distinct ItemTab (item occurs only once)
1 is related to 3 and 3 is related to 6 (designated as for example 1)
8 is related to 9 and 9 is related to 15 (designated as for example 2)
12 is related to 13 (designated as for example 3)

Is it possible?

really thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-21 : 13:28:07

yvw,

Yes, it is possible. One method would be to use the results in @Lookup and restructure them to your desired grouping. Something like this:


--using the resulting @lookup table from the sql above
--SELECT * FROM @Lookup

DECLARE @results TABLE (grp INT,item INT)
DECLARE @grpCt INT

--build a resultset for further processing
INSERT @results (grp, item)
SELECT DISTINCT d.grp, l.Item
FROM @Lookup l
JOIN 
(
	SELECT item, MIN(grp) grp 
	FROM @Lookup
	GROUP BY item
) d ON d.grp = l.grp
GROUP BY l.Item, d.grp
ORDER BY d.grp,l.item

--get our NTILE argument
SELECT @grpCt = COUNT(DISTINCT grp) 
FROM @results

--display final solution
SELECT 
	NTILE(@grpCt) OVER (ORDER BY grp ASC) as [group],
	item
FROM @results
ORDER BY grp, Item

HTH.

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-21 : 17:18:45

ok final result is displayed
when I write this value on test

SELECT '1','3' UNION ALL
SELECT '6','4' UNION ALL
SELECT '8','9' UNION ALL
SELECT '12','8' UNION ALL
SELECT '9','1'

then

1 related with 3,9,8,12 (1 related with 3, 9 with 1, 9 with 8, 8 with 12) it will be group 1
then
6 with 4 it will be group 2
result:
1..1
3..1
4..2
6..2
8..1
9..1
12..1

but your result
1 1
1 2
3 2
4 3
6 3
8 1
8 2
9 1
9 2
12 1
12 3

I know it is complicated

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-21 : 20:51:49

quote:
Originally posted by Gekko

ok final result is displayed
when I write this value on test...

I see. How about this?

DECLARE @Nodes TABLE (Parent INT, Child INT)

INSERT @Nodes (Parent, Child)
--SELECT '1','3'		UNION ALL
--SELECT '6','3'		UNION ALL
--SELECT '8','9'		UNION ALL
--SELECT '12','13'	UNION ALL
--SELECT '9','15'

SELECT '1','3'	 UNION ALL
SELECT '6','4'	 UNION ALL
SELECT '8','9'	 UNION ALL
SELECT '12','8' UNION ALL
SELECT '9','1' 

--Add reverse relations
INSERT @Nodes SELECT Child, Parent FROM @Nodes

--setup some local vars
DECLARE @items TABLE (n INT IDENTITY(0,1), item INT)
DECLARE @n INT = 0
DECLARE @Child INT
DECLARE @Lookup TABLE (grp INT, Item INT)
INSERT @items SELECT item FROM (SELECT Parent AS item FROM @Nodes UNION SELECT Child FROM @Nodes) d ORDER BY item

--begin recursion
WHILE @n <= (SELECT MAX(n) FROM @items)
BEGIN

	SELECT @Child = item FROM @items WHERE n = @n
	DECLARE @NodePaths TABLE (Path VARCHAR(8000), TopNode INT)
	INSERT @NodePaths SELECT @Child, @Child

	WHILE @@ROWCOUNT > 0
		INSERT @NodePaths
		SELECT CAST(Parent AS VARCHAR(10)) + '/' + p.Path, Parent
		FROM @NodePaths p inner join @Nodes n ON n.Child = p.TopNode
		WHERE 
			CHARINDEX('/' + CAST(Parent AS VARCHAR(10)) + '/', '/' + Path + '/') = 0 --prevent going round in circles
			and CAST(Parent AS VARCHAR(10)) + '/' + p.Path not in (select Path from @NodePaths) --prevent redoing paths already done
	SET @n+=1
END

--build nodes into normalized lookup list
INSERT @Lookup
SELECT 
	TopNode AS grp,
	value AS item
FROM
(
	SELECT 
		d.TopNode,
		LTRIM(RTRIM(NULLIF(SUBSTRING('/' + Path + '/', Number, CHARINDEX('/', '/' + Path + '/', Number) - Number), ''))) AS value
	FROM
	(
		SELECT TopNode, MAX(Path) AS Path
		FROM @NodePaths 
		WHERE CAST(TopNode AS VARCHAR(2)) != Path
		GROUP BY TopNode
	) d, master..spt_values n
	WHERE Type = 'P' 
		AND Number <= LEN('/' + d.Path + '/')
		AND SUBSTRING('/' + d.Path + '/', Number - 1, 1) = '/'
		AND CHARINDEX('/', '/' + d.Path + '/', Number) - Number > 0		
) d

--build results
DECLARE @results TABLE (grp INT,item INT, grpCt INT)

INSERT @results
SELECT l.grp, l.item, d.grpCt
FROM @Lookup l
JOIN 
(
	SELECT grp, COUNT(grp) as grpCt
	FROM @Lookup
	GROUP BY grp
) d ON d.grp = l.grp


--remove duplicates
DELETE r1
FROM @results r1
JOIN @results r2 ON r2.item = r1.item 
WHERE r1.grp < r2.grp AND r1.grpCt <= r2.grpCt


------display final solution
SELECT 
 (SELECT COUNT(DISTINCT grp) FROM @results r2 WHERE r2.grp <= r.grp) AS [group],
  r.item
FROM @results r

EDIT: Clean up formatting

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-22 : 04:42:51

oh yeah.....

you're good ....This is it

bog thanks ehorn

good luck

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-22 : 08:27:32

Here is another (linear) approach.

USE TempDB;

SET NOCOUNT ON

CREATE TABLE	dbo.Data
		(
			Item1 INT NOT NULL,
			Item2 INT NOT NULL
		)

INSERT	dbo.Data
	(
		Item1,
		Item2
	)
VALUES	( 1,  3),
	( 6,  3),
	( 8,  9),
	(12, 13),
	( 9, 15)
/*
VALUES	( 1, 3),
	( 6, 4),
	( 8, 9),
	(12, 8),
	( 9, 1)
*/
CREATE TABLE	#Groups
		(
			Item INT PRIMARY KEY CLUSTERED,
			Grp INT NOT NULL
		)

DECLARE	@Iteration INT = 0,
	@Item1 INT,
	@Item2 INT,
	@Grp1 INT,
	@Grp2 INT

DECLARE	curData CURSOR FAST_FORWARD FOR
			SELECT	Item1,
				Item2
			FROM	dbo.Data

OPEN	curData

FETCH	NEXT
FROM	curData
INTO	@Item1,
	@Item2

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET	@Iteration += 1

		SELECT	@Grp1 = MIN(CASE WHEN Item = @Item1 THEN Grp ELSE @Iteration END),
			@Grp2 = MIN(CASE WHEN Item = @Item2 THEN Grp ELSE @Iteration END)
		FROM	#Groups
		WHERE	Item IN (@Item1, @Item2)

		SELECT	@Grp1 = ISNULL(@Grp1, @Iteration),
			@Grp2 = ISNULL(@Grp2, @Iteration)
	
		IF @Grp1 <> @Grp2
			BEGIN
				UPDATE	#Groups
				SET	Grp = @Grp1
				WHERE	Grp = @Grp2

				SET	@Grp2 = @Grp1
			END

		MERGE	#Groups AS tgt
		USING	(
				VALUES	(@Item1, @Grp1),
					(@Item2, @Grp2)
			) AS src(Item, Grp) ON src.Item = tgt.Item
		WHEN	NOT MATCHED BY TARGET
				THEN	INSERT	(
							Item,
							Grp
						)
					VALUES	(
							src.Item,
							src.Grp
						);

		FETCH	NEXT
		FROM	curData
		INTO	@Item1,
			@Item2
	END

CLOSE	curData
DEALLOCATE	curData

SELECT		Item,
		Grp
FROM		#Groups
ORDER BY	Grp,
		Item

DROP TABLE	dbo.Data,
		#Groups

N 56°04'39.26"
E 12°55'05.63"

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-22 : 09:10:17

ok I will try second query....

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-22 : 10:07:24

quote:
Originally posted by Gekko

oh yeah.....

you're good ....This is it

bog thanks ehorn

good luck

np... yvw.

Do have a look at SwePeso's. It will be more performant on large data sets.

Thanks for sharing SwePeso.

Have a nice day guys.

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-22 : 13:47:52

ok....it is good, otherwise it is very goog. When values are int... great.

And what if:
some values are for example:

VALUES ( 1, 3),
( 6, 3),
( 8, k9),
(a12, a13),
( k9, 6)
Can I change the int to varchar? (dbo.Data,#Groups etc)
what you say, SwePeso?

CREATE TABLE dbo.Data
(
Item1 varchar ,
Item2 varchar
CREATE TABLE #Groups
(
Item varchar ,
Grp INT
it is possible change int on varchar?
I try this change and values displayed "INVALID COLUMN NAME k9"
or what should be changed?

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-22 : 14:37:33

Sure. Just make VARCHAR have a specified length.

USE TempDB;

SET NOCOUNT ON

CREATE TABLE	dbo.Data
		(
			Item1 VARCHAR(20) NOT NULL,
			Item2 VARCHAR(20) NOT NULL
		)

INSERT	dbo.Data
	(
		Item1,
		Item2
	)
VALUES	('1', '3'),
	('6', '3'),
	('8', 'k9'),
	('a12', 'a13'),
	('k9', '6')

CREATE TABLE	#Groups
		(
			Item VARCHAR(20) PRIMARY KEY CLUSTERED,
			Grp INT NOT NULL
		)

DECLARE	@Iteration INT = 0,
	@Item1 VARCHAR(20),
	@Item2 VARCHAR(20),
	@Grp1 INT,
	@Grp2 INT

DECLARE	curData CURSOR FAST_FORWARD FOR
			SELECT	Item1,
				Item2
			FROM	dbo.Data

OPEN	curData

FETCH	NEXT
FROM	curData
INTO	@Item1,
	@Item2

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET	@Iteration += 1

		SELECT	@Grp1 = MIN(CASE WHEN Item = @Item1 THEN Grp ELSE @Iteration END),
			@Grp2 = MIN(CASE WHEN Item = @Item2 THEN Grp ELSE @Iteration END)
		FROM	#Groups
		WHERE	Item IN (@Item1, @Item2)

		SELECT	@Grp1 = ISNULL(@Grp1, @Iteration),
			@Grp2 = ISNULL(@Grp2, @Iteration)
	
		IF @Grp1 <> @Grp2
			BEGIN
				UPDATE	#Groups
				SET	Grp = @Grp1
				WHERE	Grp = @Grp2

				SET	@Grp2 = @Grp1
			END

		MERGE	#Groups AS tgt
		USING	(
				VALUES	(@Item1, @Grp1),
					(@Item2, @Grp2)
			) AS src(Item, Grp) ON src.Item = tgt.Item
		WHEN	NOT MATCHED BY TARGET
				THEN	INSERT	(
							Item,
							Grp
						)
					VALUES	(
							src.Item,
							src.Grp
						);

		FETCH	NEXT
		FROM	curData
		INTO	@Item1,
			@Item2
	END

CLOSE	curData
DEALLOCATE	curData

SELECT		Item,
		Grp
FROM		#Groups
ORDER BY	Grp,
		Item

DROP TABLE	dbo.Data,
		#Groups

N 56°04'39.26"
E 12°55'05.63"

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-22 : 14:56:05

Perfect

big thanks

SwePeso and ehorn

good luck

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-22 : 17:14:03

Here is a faster implementation. Inserts happens only once.

USE TempDB;

SET NOCOUNT ON

CREATE TABLE	dbo.Data
		(
			Item1 VARCHAR(20) NOT NULL,
			Item2 VARCHAR(20) NOT NULL
		)

INSERT	dbo.Data
	(
		Item1,
		Item2
	)
VALUES	('1', '3'),
	('6', '3'),
	('8', 'k9'),
	('a12', 'a13'),
	('k9', '6')

CREATE TABLE	#Groups
		(
			Item VARCHAR(20) PRIMARY KEY CLUSTERED,
			Grp INT NOT NULL
		)

INSERT		#Groups
		(
			Item,
			Grp
		)
SELECT 		f.Item,
		MIN(d.Grp) AS Grp
FROM		(
			SELECT	Item1,
				Item2,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Grp -- If clustered key exists in dbo.Data, choose ORDER BY ClusteredKeyColumn instead
			FROM	dbo.Data
		) AS d
CROSS APPLY	(
			VALUES	(d.Item1),
				(d.Item2)
		) AS f(Item)
GROUP BY	f.Item

DECLARE	@Item1 VARCHAR(20),
	@Item2 VARCHAR(20),
	@Grp1 INT,
	@Grp2 INT

DECLARE	curData CURSOR FAST_FORWARD FOR
			SELECT	Item1,
				Item2
			FROM	dbo.Data

OPEN	curData

FETCH	NEXT
FROM	curData
INTO	@Item1,
	@Item2

WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT	@Grp1 = MAX(CASE WHEN Item = @Item1 THEN Grp ELSE 0 END),
			@Grp2 = MAX(CASE WHEN Item = @Item2 THEN Grp ELSE 0 END)
		FROM	#Groups
		WHERE	Item IN (@Item1, @Item2)

		IF @Grp1 <> @Grp2
			UPDATE	#Groups
			SET	Grp = @Grp1
			WHERE	Grp = @Grp2

		FETCH	NEXT
		FROM	curData
		INTO	@Item1,
			@Item2
	END

CLOSE	curData
DEALLOCATE	curData

SELECT		Item,
		Grp
FROM		#Groups
ORDER BY	Grp,
		Item

DROP TABLE	dbo.Data,
		#Groups

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-01-22 : 17:39:46

Some more tweaking...

USE TempDB;

SET NOCOUNT ON

CREATE TABLE	dbo.Data
		(
			Item1 VARCHAR(20) NOT NULL,
			Item2 VARCHAR(20) NOT NULL
		)

INSERT	dbo.Data
	(
		Item1,
		Item2
	)
VALUES	('1', '3'),
	('6', '3'),
	('8', 'k9'),
	('a12', 'a13'),
	('k9', '6')

CREATE TABLE	#Groups
		(
			Item VARCHAR(20) NOT NULL,
			Grp INT NOT NULL
		)

INSERT		#Groups
		(
			Item,
			Grp
		)
SELECT 		f.Item,
		MIN(d.Grp) AS Grp
FROM		(
			SELECT	Item1,
				Item2,
				ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Grp
			FROM	dbo.Data
		) AS d
CROSS APPLY	(
			VALUES	(d.Item1),
				(d.Item2)
		) AS f(Item)
GROUP BY	f.Item

CREATE NONCLUSTERED INDEX IX_Item ON #Groups (Item) INCLUDE (Grp)
CREATE NONCLUSTERED INDEX IX_Grp ON #Groups (Grp)

DECLARE	@Item1 VARCHAR(20),
	@Item2 VARCHAR(20),
	@Grp1 INT,
	@Grp2 INT

DECLARE	curData CURSOR FAST_FORWARD FOR
			SELECT	Item1,
				Item2
			FROM	dbo.Data

OPEN	curData

FETCH	NEXT
FROM	curData
INTO	@Item1,
	@Item2

WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT		@Grp1 = MAX(CASE WHEN g.Item = @Item1 THEN g.Grp ELSE 0 END),
				@Grp2 = MAX(CASE WHEN g.Item = @Item2 THEN g.Grp ELSE 0 END)
		FROM		#Groups AS g
		INNER JOIN	(
					VALUES	(@Item1),
						(@Item2)
				) AS f(Item) ON f.Item = g.Item

		IF @Grp1 <> @Grp2
			UPDATE	g
			SET	Grp = @Grp1
			FROM	#Groups AS g WITH (INDEX (IX_Grp))
			WHERE	Grp = @Grp2

		FETCH	NEXT
		FROM	curData
		INTO	@Item1,
			@Item2
	END

CLOSE	curData
DEALLOCATE	curData

SELECT		Item,
		Grp
FROM		#Groups

DROP TABLE	dbo.Data,
		#Groups

N 56°04'39.26"
E 12°55'05.63"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-22 : 17:49:39

Gekko
Yak Posting Veteran

63 Posts

Posted - 2012-01-23 : 01:22:11

but for me it's enough
boys are very kind
I try fastes query.
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources