SQLTeam.com | Weblogs | Forums

Possible combinations needed in iterative method


#1

I have a table having three column
Name Value1 Value2 Value3
A 1 2 3
B 4 5 1
C 2 3 1
I want to output like that
Name Value1 Value2 Value3

A 1 2 3
B 4 5 1
C 2 3 1
A+c 3 5 4
A+B 5 7 4
B+C 6 8 2
A+B+C 7 10 5
I want to generate as above, i want to generate all the possible combinations the above example contains 3 rows A B and c so the possible combinations are A, B,C,AB,AC,BC,ABC
I NEED TO GENERATE LIKE THIS UPTO 100 ROWS .
correctly i have the code in recusive method that supports upto 9 rows when exceeds it misses so many rows. That because of maxrecursion i think, i have tried it by increasing it , but application goes not responding and time outs. Even tried increasing time out. Some one help me with this please ..
Some one can help me this please. I have added the code used for this function below.
USE [myappdb]
GO
/****** Object: StoredProcedure [dbo].[proc_geneatecombkachha] Script Date: 11/27/2016 00:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_geneatecombkachha]
as
begin
delete from TempComb

Declare @NumberOfRows int;
Set @NumberOfRows = (Select Count(*) From KacchaStock)
Declare @Numbers Table(Number int);

insert into TempComb
select KPID,KPWeight,kpTouch,KPPure from KacchaStock
;With cte As
(Select 1 As Number
Union All
Select Number + 1
From cte
Where Number < Power(2, @NumberOfRows))--@MaxPowerValue * 2 - 1)
Insert @Numbers(Number)
Select Number From cte option (maxrecursion 0);

Declare @Summed Table(Name varchar(10), Value1 int, Value2 int,Value3 int, Number int);

;With cteGrouped As
(Select KPID, KPWeight, KPPure, KPTouch, Row_Number() Over(Order By KPID) As rn
From KacchaStock)
Insert @Summed(Name, Value1, Value2, Value3, Number)
Select KPID, Sum(KPWeight) Over(Partition By Number) As Value1,
Sum(KPTouch) Over(Partition By Number) As Value2,
Sum(KPPure) Over(Partition By Number) As Value3,
Number
From cteGrouped c
Cross Join @Numbers n
Where Power(2, rn - 1) & Number = Power(2, rn - 1)
Order By n.Number, KPID;

;With cte As
(Select Distinct Stuff((Select ',' + s2.Name
From @Summed s2 Where s.Number = s2.Number
Order By s2.Name
For XML Path('')),1,1,'') As Combination,
s.Value1, s.Value2, s.Value3
From @Summed s)
insert into TempComb
Select Combination, Value1 as Weight, (Value3*100)/Value1 as Touch, Value3 as Pure
From cte
Where PatIndex('%,%', Combination) > 1;

select * from TempComb

end


#2

Try this:

with cte1(Name,Value1,Value2,Value3)
  as (select cast(Name as varchar(20))
            ,Value1
            ,Value2
            ,Value3
        from test..KacchaStock
     )
    ,cte2(Name,Value1,Value2,Value3)
  as (select Name
            ,Value1
            ,Value2
            ,Value3
        from cte1
      union all
      select cast(b.Name+'+'+a.Name as varchar(20))
            ,b.Value1+a.Value1
            ,b.Value1+a.Value2
            ,b.Value1+a.Value3
        from cte1 as a
             inner join cte2 as b
                     on substring(reverse(b.Name),1,1)<substring(reverse(a.Name),1,1)
     )
select Name
      ,Value1
      ,Value2
      ,Value3
  from cte2
;