December 26, 2012 at 8:32 am
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
GroupIdItemIdItemName
1100Bike
1200Bottle
2100Bike
2300Helmet
3200Bottle
3300Helmet
4400Bread
4500Egg
Required Output
GroupIdItemIdItemName
1100Bike
1200Bottle
1300Helmet
4400Bread
4500Egg
December 26, 2012 at 8:48 am
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;
December 26, 2012 at 9:29 am
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.
GroupMinGroupIdItemIdItemName
1100Bike
1200Bottle
1300Helmet
4400Bread
4500Egg
Thanks Kl25.
December 26, 2012 at 9:52 am
Glad the approach was helpful. I missed the slight variation in the output.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply