• 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)