I'm going to see if I can do this. In the meantime, if anyone else wants to try I've created some more usable sample data.
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Products')
IS NOT NULL DROP TABLE #Products;
IF OBJECT_ID('tempdb..#Groups')
IS NOT NULL DROP TABLE #Groups;
IF OBJECT_ID('tempdb..#Languages')
IS NOT NULL DROP TABLE #Languages;
IF OBJECT_ID('tempdb..#GroupRelations')
IS NOT NULL DROP TABLE #GroupRelations;
IF OBJECT_ID('tempdb..#GroupProductRelation')
IS NOT NULL DROP TABLE #GroupProductRelation;
SELECT * INTO #Products FROM (VALUES
('100101@@SHOP1','LANG2','SHOP1',100101,'AAA','159,61'),
('100102@@SHOP1','LANG2','SHOP1',100102,'BBB','159,61'),
('100105@@SHOP1','LANG2','SHOP1',100105,'CCC','159,61')) Products
(
ProductID,
ProductLanguageID,
ProductDefaultShopID,
ProductNumber,
ProductName,
ProductPrice
);
SELECT * INTO #Groups FROM (VALUES
('1@@SHOP1','LANG2','ABC',1),
('1.01@@SHOP1','LANG2','BCD',1.01),
('1.02@@SHOP1','LANG2','CDE',1.02),
('1.03@@SHOP1','LANG2','DEF',1.03)) Groups
(
GroupID,
GroupLanguageID,
GroupName,
GroupNumber
);
SELECT * INTO #Languages FROM (VALUES
('LANG1','En','English','English'),
('LANG2','De','German','Deutsch')) Languages
(
LanguageID,
LanguageCode2,
LanguageName,
LanguageNativeName
);
SELECT * INTO #GroupRelations FROM (VALUES
('1.01@@SHOP1','1@@SHOP1'),
('1.02@@SHOP1','1@@SHOP1'),
('1.02@@SHOP1','1@@SHOP1')) GroupRelations
(
GroupRelationsGroupID,
GroupRelationsParentID
);
SELECT * INTO #GroupProductRelation FROM (VALUES
('1.01@@SHOP1','576134@@SHOP1'),
('1.01@@SHOP1','327112@@SHOP1'),
('1.01@@SHOP1','457168@@SHOP1'),
('1.01@@SHOP1','457220@@SHOP1'),
('1.03@@SHOP1','457221@@SHOP1'),
('1.03@@SHOP1','163704@@SHOP1'),
('1.03@@SHOP1','163705@@SHOP1'),
('1.03@@SHOP1','163706@@SHOP1')) GroupProductRelation
(
GroupProductRelationGroupID,
GroupProductRelationProductID
);
[/code]
Edit: used the wrong IFCode shortcut for my sample data.
-- Itzik Ben-Gan 2001