And another with the same technique:CREATE TABLE #Template (
TaskName VARCHAR(20),
TemplateName VARCHAR(20)
);
INSERT INTO #Template (TaskName, TemplateName)
SELECT 'Task1', 'Template 1' UNION ALL
SELECT 'Task2', 'Template 1' UNION ALL
SELECT 'Task3', 'Template 1' UNION ALL
SELECT 'Task4', 'Template 1'
SELECT *
FROM #Template;
CREATE TABLE #TasksByPlan (
TaskName VARCHAR(20),
TemplateName VARCHAR(20),
PlanName VARCHAR(20)
);
INSERT INTO #TasksByPlan (TaskName, TemplateName, PlanName)
SELECT 'Task1', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task3', 'Template 1', 'Plan1' UNION ALL
SELECT 'Task1', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task3', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task4', 'Template 1', 'Plan2' UNION ALL
SELECT 'Task1', 'Template 1', 'Plan3' UNION ALL
SELECT 'Task2', 'Template 1', 'Plan3';
SELECT TBP.TemplateName, TBP.PlanName, TBP.TaskName
FROM #TasksByPlan AS TBP
ORDER BY TBP.TemplateName, TBP.PlanName, TBP.TaskName;
WITH TasksByPlan AS (
SELECT DISTINCT TBP.TemplateName, TBP.PlanName
FROM #TasksByPlan AS TBP
),
TaskTemplate AS (
SELECT DISTINCT T.TemplateName, T.TaskName
FROM #Template AS T
)
INSERT INTO #TasksByPlan (TaskName, TemplateName, PlanName)
SELECT TT.TaskName, TT.TemplateName, TBP.PlanName
FROM TaskTemplate AS TT
INNER JOIN TasksByPlan AS TBP
ON TT.TemplateName = TBP.TemplateName
AND TBP.PlanName + TT.TaskName NOT IN (
SELECT TBP2.PlanName + TBP2.TaskName
FROM #TasksByPlan AS TBP2
WHERE TBP2.TemplateName = TBP.TemplateName
);
SELECT TBP.TemplateName, TBP.PlanName, TBP.TaskName
FROM #TasksByPlan AS TBP
ORDER BY TBP.TemplateName, TBP.PlanName, TBP.TaskName;
DROP TABLE #TasksByPlan;
DROP TABLE #Template;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)