CODEWITHSUNDEEP
sqlsql-server
user2647784
user2647784

Reputation: 428

Sql Query : Sum , all the possible combination of rows in a table

SQL Server 2008 R2

Sample Table structure

create table TempTable
(
    ID int identity,
    value int
)

insert into TempTable values(6)
insert into TempTable values(7)
insert into TempTable values(8)
insert into TempTable values(9)
insert into TempTable values(10)

I actually want something like this It returns rows such that, it has the sum of all the remaining rows , as shown below.

6+7
6+8
6+9
6+10

6+7+8
6+7+9
6+7+10


6+8+9
6+8+10

6+9+10

6+7+8+9

6+7+8+9+10

AronLS solution is meeting my needs. However keeping the question open for few days. 1. Is there another/better way I can do this ?

Upvotes: 3

Views: 3948

Answers (2)

Jim the Frayed
Jim the Frayed

Reputation: 158

FWIW-- With a little embellishment, you can see what's happening:

CREATE TABLE #TempTable
(
    Expr VARCHAR(10),
    ID INT IDENTITY,
    value INT
)

INSERT INTO #TempTable VALUES('6', 6)  -- a
INSERT INTO #TempTable VALUES('7', 7)  -- b
INSERT INTO #TempTable VALUES('8', 8)  -- c
INSERT INTO #TempTable VALUES('9', 9)  -- d
INSERT INTO #TempTable VALUES('10', 10) -- e

SELECT expr, value FROM #TempTable

-- Choose(n,k) = n!/(n!(n-k)!)  [order not important]
-- n = 5, k = {1,2,3,4,5}

-- C(5,1) + C(5,2) + C(5,3) + C(5,4) + C(5,5)      
--    5   +   10   +   10   +   5    +   1     = 31
-- There should be 31 rows.
------
;
WITH  selfrecCTE AS
( 
   SELECT
      CAST(expr AS VARCHAR(100)) AS Expr
      , T.value
      , T.ID
      , 0 AS Level
   FROM
      #TempTable AS T
   UNION ALL
   SELECT
      CAST(t1.expr + ' + ' + T2.expr AS VARCHAR(100)) AS Expr
      , T2.value + T1.value AS Value
      , T1.ID
      , Level + 1
   FROM
      #TempTable T1
      INNER JOIN selfrecCTE T2
         ON T1.ID < T2.ID
   --WHERE
   --  Level < 4 -- limit the number of recursions
)
   -- 31 rows. OK.
   SELECT  * FROM selfrecCTE
   ORDER By id, expr, Level

I found this to be an amusing diversion. Thanks for the question!

Upvotes: 1

AaronLS
AaronLS

Reputation: 38394

This is a "all possible permutations" problem can be solved with a self join and a recursive CTE.

With selfrec as (
  Select t.Value, t.ID, 0 as Level From TempTable t
       UNION ALL
  Select t2.Value + t1.Value as Value, t1.ID, Level + 1 From TempTable t1 
  Inner Join selfrec t2 on t1.ID < t2.ID 
  Where Level < 4 -- limit the number of recursions
)
Select * From selfrec
Order By Level

This criteria eliminates items matching themselves, as well as having duplicate reversals of permutation combos: t1.ID < t2.ID

Limits the depth of recursion since we have no other breaking criteria and will surpass the number of allowed recursions: Where Level < 4

Upvotes: 5

Related Questions