Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Traversing a tree Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 236, Visits: 439
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
Posted Wednesday, December 26, 2012 8:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 488, Visits: 1,859
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
Posted Wednesday, December 26, 2012 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 236, Visits: 439
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
Posted Wednesday, December 26, 2012 9:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 488, Visits: 1,859
Glad the approach was helpful. I missed the slight variation in the output.
Post #1400291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse