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