Getting all possible combinations that obey a certain condition with MS SQL

Question

I need to create a SQL query, but I do not know how to do this. If someone helps, I will give in 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 groupedBYField, SUM(ToBeSummeField)that have SUM(conditionField) = 2, i.e. 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 0 values ​​appear. It will work on SQL 2005/2008.

It uses a recursive CTE to process 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 t.id
               ,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 work on any number of source lines