Traversing a tree

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Traversing a tree

Traversing a tree

Rate Topic

Display Mode

Topic Options

Author

Message

U.T

Posted Wednesday, December 26, 2012 8:32 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

I have different groups of items, some items are common in groups. I need a query that can combine groups if there is atleast one common item in groups.

Here is the sample data with current output and required output.

SELECT 1 AS GroupId, 100 AS ItemId, 'Bike' AS ItemName INTO #Data
UNION ALL
SELECT 1 AS GroupId, 200 AS ItemId, 'Bottle' AS ItemName
UNION ALL
SELECT 2 AS GroupId, 100 AS ItemId, 'Bike' AS ItemName
UNION ALL
SELECT 2 AS GroupId, 300 AS ItemId, 'Helmet' AS ItemName
UNION ALL
SELECT 3 AS GroupId, 200 AS ItemId, 'Bottle' AS ItemName
UNION ALL
SELECT 3 AS GroupId, 300 AS ItemId, 'Helmet' AS ItemName
UNION ALL
SELECT 4 AS GroupId, 400 AS ItemId, 'Bread' AS ItemName
UNION ALL
SELECT 4 AS GroupId, 500 AS ItemId, 'Egg' AS ItemName

SELECT *
FROM #Data

Current Output

GroupId ItemId ItemName
1 100 Bike
1 200 Bottle
2 100 Bike
2 300 Helmet
3 200 Bottle
3 300 Helmet
4 400 Bread
4 500 Egg

Required Output

GroupId ItemId ItemName
1 100 Bike
1 200 Bottle
1 300 Helmet
4 400 Bread
4 500 Egg

Post #1400262

kl25

Posted Wednesday, December 26, 2012 8:48 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 243, Visits: 1,104

Your desired output seems to suggest that what you're looking for is the minimum GroupID for each item.

Select MIN(GroupId) as GroupId, ItemId, ItemName
From #Data
Group by ItemId, ItemName
Order by ItemId;

Post #1400268

U.T

Posted Wednesday, December 26, 2012 9:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407

Kl25, thanks a lot for the idea. Your query gave a slightly different result but i used your idea to generate the required result.

Basically first find the minimum group id for each item, then find the minimum group id (based on item's min group id) for each group.

SELECT DISTINCT c.GroupMinGroupId, a.ItemId, a.ItemName
FROM #Data a
INNER JOIN
(
SELECT MIN(b.ItemMinGroupId) AS GroupMinGroupId, a.GroupId
FROM #Data a
INNER JOIN
(
Select MIN(GroupId) as ItemMinGroupId, ItemId
From #Data
Group by ItemId
) b ON a.ItemId = b.ItemId
GROUP BY a.GroupId
) c ON a.GroupId = c.GroupId

Here is the output.

GroupMinGroupId ItemId ItemName
1 100 Bike
1 200 Bottle
1 300 Helmet
4 400 Bread
4 500 Egg

Thanks Kl25.

Post #1400282

kl25

Posted Wednesday, December 26, 2012 9:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 243, Visits: 1,104

Glad the approach was helpful. I missed the slight variation in the output.

Post #1400291

« Prev Topic | Next Topic »

Permissions