September 20, 2010 at 9:04 am
DECLARE @MyRelations TABLE
(
SubjectID VARCHAR(8),
RelatedID VARCHAR(8)
)
INSERT @MyRelations
SELECT 'A', 'B' UNION
SELECT 'A', 'C' UNION
SELECT 'C', 'D' UNION
SELECT 'B', 'E' UNION
SELECT 'X', 'Y' UNION
SELECT 'X', 'Z'
This data define some how A, B, C, D, E are related and X,Y,Z are related
I want a result set which has grouped all the relations
Like
/* My desired result set */
RelationID MyData
1A
1B
1C
1D
1E
2X
2Y
2Z
September 20, 2010 at 9:33 am
Why would xyz come to 2 and abcde be at 1 ?
September 20, 2010 at 9:56 am
ColdCoffee (9/20/2010)
Why would xyz come to 2 and abcde be at 1 ?
It can be other way around too. abcde are related and we give an id then we see xyz are related and give next id.
If you ask how abcde are related
A related B & A related to C so B is related to C
A related B & B related to E so A is related to E
similarly we can say A, B, C, D, E are related with each other and we give them a id any id is okay.
similarly we can say X, Y, Z are related and we give any other id that is not already given.
September 20, 2010 at 9:59 am
I got a nasty solution. I don't like this as this is time consuming with large data. I would like a set based solution.
DECLARE @MyRelations TABLE
(
SubjectID VARCHAR(8),
RelatedID VARCHAR(8)
)
INSERT @MyRelations
SELECT 'A', 'B' UNION
SELECT 'A', 'C' UNION
SELECT 'C', 'D' UNION
SELECT 'B', 'E' UNION
SELECT 'X', 'Y' UNION
SELECT 'X', 'Z'
DECLARE @AllID TABLE
(
SubjectID VARCHAR(8)
)
INSERT @AllID
SELECT * FROM
(
SELECT DISTINCT SubjectID FROM @MyRelations
UNION
SELECT DISTINCT RelatedID FROM @MyRelations
) A
DECLARE @MyVal VARCHAR(8)
DECLARE @GroupID INT
SELECT @GroupID = 0
DECLARE @MyResults TABLE
(
GroupID INT,
SubjectID VARCHAR(8)
)
WHILE EXISTS (SELECT * FROM @AllID)
BEGIN
SELECT @GroupID = @GroupID + 1
SELECT @MyVal = MIN(SubjectID) FROM @AllID
INSERT @MyResults
SELECT @GroupID, @MyVal
WHILE (@@ROWCOUNT > 0)
BEGIN
INSERT @MyResults
SELECT * FROM
(
SELECT @GroupID GroupID, SubjectID
FROM
@MyRelations
WHERE
SubjectID IN (SELECT SubjectID FROM @MyResults WHERE GroupID = @GroupID)
OR
RelatedID IN (SELECT SubjectID FROM @MyResults WHERE GroupID = @GroupID)
UNION
SELECT @GroupID, RelatedID
FROM
@MyRelations
WHERE
SubjectID IN (SELECT SubjectID FROM @MyResults WHERE GroupID = @GroupID)
OR
RelatedID IN (SELECT SubjectID FROM @MyResults WHERE GroupID = @GroupID)
) P
WHERE
SubjectID NOT IN (SELECT SubjectID FROM @MyResults WHERE GroupID = @GroupID)
END
DELETE A
FROM
@AllID A
JOIN
@MyResults R
ON
A.SubjectID = R.SubjectID
END
SELECT * FROM @MyResults
September 20, 2010 at 2:39 pm
I'm not sure you can avoid looping or recursion to achieve the desired result, at least not efficiently. The following uses a single WHILE loop rather than nested loops. The idea is to identify each set of linked relations by the minimum ID value in that set.
The initial step is to build a worktable of relations where the MinID column holds the smaller of the two related IDs from the original @MyRelations table, and the RefID holds the larger ID value.
The WHILE loop then iteratively looks for pairs of rows from the worktable where the MinID column of the right-hand row matches the RefID column of the left-hand row, which implies that the two relations are members of the same set of linked relations and that the MinID column value of the right-hand row can be updated with the MinID column value of the left-hand row. Processing is finished when an iteration results in no more rows being updated.
DECLARE @WorkTable TABLE (
MinID varchar(8),
RefID varchar(8)
)
INSERT INTO @WorkTable(MinID, RefID)
SELECT SubjectID, RelatedID FROM @MyRelations WHERE (SubjectID < RelatedID)
UNION ALL
SELECT RelatedID, SubjectID FROM @MyRelations WHERE (RelatedID < SubjectID)
WHILE (@@ROWCOUNT <> 0) BEGIN
UPDATE WTR SET MinID = WTL.MinID
FROM @WorkTable WTL
INNER JOIN @WorkTable WTR ON (WTL.RefID = WTR.MinID)
END
/* Produce required result from worktable */
SELECT DENSE_RANK() OVER (ORDER BY MinID) AS RelationID, RefID AS MyData
FROM (
SELECT MinID, RefID FROM @WorkTable
UNION ALL
SELECT DISTINCT MinID, MinID FROM @WorkTable
) U
ORDER BY RelationID, MyData
September 21, 2010 at 1:17 pm
Nice logic. leaning TSQL from SQL Server 7.0, I am not thinking about rank functions.
Thank You.
I would like to keep this open to get another elegant solution.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply