Getting all possible combinations which obey certain condition with MS SQL

Question

I need to constract an SQL query but I have no idea how to do it. If someone helps, I'll appriciate it very much.

I have the following table

GroupedBYField          ConditionField          ToBeSummeField
     1                        1                      1
     1                        1                      2
     1                        1                      3
     2                        2                     100
     2                        2                     200
     2                        2                     300

and I need to get all the possible combinations of groupedBYField, SUM(ToBeSummeField) which has SUM(conditionField) = 2, that is the following table

GroupedBYField          SumField
     1                     3
     1                     4
     1                     5
     2                    100
     2                    200
     2                    300

Thank you for your help!

Answer 1

I believe this works. It should also work where ConditionField values of 0 appear.
It will run on SQL 2005/2008.

It uses a recursive CTE to deal with any number of potential rows adding to the required value

DECLARE @t TABLE 
(GroupedBYField INT
,ConditionField INT
,ToBeSummeField INT
)

INSERT @t
      SELECT 1,1,1
UNION SELECT 1,1,2
UNION SELECT 1,1,3
UNION SELECT 2,2,100
UNION SELECT 2,2,200
UNION SELECT 2,2,300


;WITH numCTE
AS
(
        SELECT ROW_NUMBER() OVER (ORDER BY GroupedBYField
                                           ,ConditionField
                                           ,ToBeSummeField
                                 ) AS id
               ,*
        FROM @t               
)
,myCTE
AS
(
        SELECT id
               ,GroupedBYField
               ,ConditionField
               ,ToBeSummeField
               ,'|' + CAST(id AS VARCHAR(MAX)) + '|' AS LEVEL
        FROM numCTE

        UNION ALL

        SELECT CASE WHEN t.id > m.id
                    THEN t.id
                    ELSE m.id
               END
               ,t.GroupedBYField
               ,m.ConditionField + t.ConditionField
               ,m.ToBeSummeField + t.ToBeSummeField
               ,m.LEVEL + '|' + CAST(t.id AS VARCHAR(11)) + '|' AS LEVEL
        FROM myCTE  AS m
        JOIN numCTE AS t
        ON   t.id             > m.id
        AND  t.GroupedBYField = m.GroupedBYField
        AND  m.LEVEL NOT LIKE '%|' + CAST(t.id AS VARCHAR(MAX)) + '|%'

)
SELECT GroupedBYField
       ,ToBeSummeField
FROM myCTE
WHERE ConditionField = 2 -- amend this value change the target sum
ORDER BY 1,2
OPTION (MAXRECURSION 0)

EDIT - added maxrecursion 0 to permit this to work on any number of source rows