possible combinations for all rows in table
-
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
-
This piece of code will not work for @NumberOfRows > 31.
urbanromio2 (11/27/2016)
Declare @NumberOfRows int;
Declare @Numbers Table(Number int);
;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);
The reason that it will not work, is that
* SELECT POWER(2, 100) will result in an error Arithmetic overflow error for type int
* If you do manage to generate that many numbers, you will not be able to add them to @Numbers, as the value will be much larger than the max value for INT. It will even be too big for BIGINT ... See https://msdn.microsoft.com/en-us/library/ms187745.aspx
Also note that
* the recursive CTE to generate that many numbers is going to be very inefficient
* a table variable or a temp table to hold that much data is going to put a big hit on TempDB
-
Can u tell me how to get then?
-
The code does not works, goes not responsible for 13 rows itself didnt try .. That could be fine if it works for 30 rows at least
-
See if this helps
DECLARE @t TABLE(Name VARCHAR(10) NOT NULL PRIMARY KEY,Value1 INT NOT NULL,
Value2 INT NOT NULL,
Value3 INT NOT NULL);
INSERT INTO @t(Name,Value1,Value2,Value3)
VALUES('A', 1, 2, 3),
('B', 4, 5, 1),
('C', 2, 3, 1);
WITH CTE AS (
SELECT Name,Value1,Value2,Value3,CAST(Name AS VARCHAR(1000)) AS FullName
FROM @t
UNION ALL
SELECT t.Name,
c.Value1 + t.Value1,
c.Value2 + t.Value2,
c.Value3 + t.Value3,
CAST(c.FullName + '+' + t.Name AS VARCHAR(1000)) AS FullName
FROM CTE c
INNER JOIN @t t ON t.Name > c.Name)
SELECT FullName AS Name,
Value1,
Value2,
Value3
FROM CTE
ORDER BY LEN(FullName),FullName;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I cannot imagine any method that will not be a massive hit on your server.
Perhaps if you explained why you need to create such a massive amount data, some of the more experienced guys could help you find a better design.
That said, the following Virtual Tally Table will work for up to 48 items.
NOTE: This is still a massive amount of data to generate, and WILL be slow. You will probably need to change this to a permanent Tally Table.
ALTER FUNCTION dbo.GetNums(@Nums AS BIGINT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH lv0(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0)
,(0),(0),(0),(0),(0),(0),(0),(0) ) l(N) )
, lv1(n) AS (SELECT 0 FROM lv0 a CROSS JOIN lv0 b)
, lv2(n) AS (SELECT 0 FROM lv1 a CROSS JOIN lv1 b)
, lv3(n) AS (SELECT 0 FROM lv2 a CROSS JOIN lv2 b)
SELECT TOP(@Nums) N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM lv3;
GO
Then you join to this tally table, instead of your rCTE.
;WITH cteGrouped AS (
SELECT KPID, KPWeight, KPPure, KPTouch
, ROW_NUMBER() OVER(ORDER BY KPID) AS rn
FROM @KacchaStock
)
SELECT KPID,
SUM(KPWeight) OVER(PARTITION BY n.N) AS Value1,
SUM(KPTouch) OVER(PARTITION BY n.N) AS Value2,
SUM(KPPure) OVER(PARTITION BY n.N) AS Value3,
n.N
FROM cteGrouped c
CROSS JOIN dbo.GetNums(POWER(@Base, @NumberOfRows)-1) AS n
WHERE POWER(@Base, rn - 1) & n.N = POWER(@Base, rn - 1)
ORDER BY n.N, KPID;
[/code]
-
The original poster has posted a second time in this same forum area, and posted only his code, with none of this level of detail. I read that post and responded with the problem with the POWER function in terms of it's ability to "scale", and asked what the actual objective was, prior to seeing this post. Now that I know what the objective is, there's no viable way they'll ever get to 100 records, and the larger the number of records, the worse this problem will get. There's just no viable way that even 30 records is going to respond within a reasonable time-frame. The objective here is just too broad and too deep for current technology to handle without seriously maxing out resource usage for a rather long time. I'm far more interested at this point in the "why" behind the need for all possible combinations, and whether or not there might be combinations that in the real world, just don't make sense, and thus aren't worth generating. If there were; for example; a strict (and small) limit on the number of elements that could be combined to create a valid combination, this might at least be possible, but even then, it's not going to perform well for anything but the smallest numbers of records. Getting to 30 records, and requiring ALL possible combinations, means you need an extraordinarily large number of records that might not even be possible to put into a single table. It might be plausible to separate out each number of elements and have all the possible combinations of that number of elements in it's own table. However, even doing any kind of practical combining of that data from multiple such tables would get impractically expensive from a time and resource usage perspective.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Come to think of it, this sort of resembles trying to compute all the possible combinations of "N things, R at a time", which is what you might do if you were seeking to analyze a lottery, such as PowerBall. The number of combinations of just 69 things, 5 at a time, multiplied by the number of possible values for the last ball (or PowerBall), is in excess of 160 million. And this poster was originally hoping to get ALL possible numbers of elements at a time, for 100 things. Even if you settle for just 30, that's 28 different but similarly numerically challenging numbers of elements to deal with, plus the rather obvious and simple setup of just 1 element, or all 30.
Hey urbanromio2, ... what can you tell about the "why" for this?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The reason is,
I have a record with 3 columns of values.
I need Specific value that comes when combining some records , so that i can enter the value which is required, the table shows combination which can be combined to get the value i required. This program for helping me to choose the records to get required total value
-
The reason is,
I have a record with 3 columns of values.
I need Specific value that comes when combining some records , so that i can enter the value which is required, the table shows combination which can be combined to get the value i required. This program for helping me to choose the records to get required total value
-
urbanromio2 (11/28/2016)
The reason is,I have a record with 3 columns of values.
I need Specific value that comes when combining some records , so that i can enter the value which is required, the table shows combination which can be combined to get the value i required. This program for helping me to choose the records to get required total value
Maybe it's a language barrier, but what you just posted says absolutely nothing about the
"why" of what you are trying to accomplish. You basically ended up saying that you need it because you need it, and that won't do anything to solve the scale problem you have here. I want to know what this existing set of data actually represents in the real world, that you need all possible combinations of those records, and why you need the sums of those record combinations. Please be specific instead of trying to generalize.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
There are several materials with 3 attributes .
Requirement will be for attributes with specific value ranges,
That may not be available with a single material. So can combine 2 or more materials to get the required attribute.
So what i thought, lets create all the possible combinations and filter the data with required attributes.
-
urbanromio2 (11/28/2016)
There are several materials with 3 attributes .Requirement will be for attributes with specific value ranges,
That may not be available with a single material. So can combine 2 or more materials to get the required attribute.
So what i thought, lets create all the possible combinations and filter the data with required attributes.
Well, we're at least one step closer. To get realistic with this, we'll need to introduce that filtering to eliminate combinations that can't possibly work. Now you'll have to provide details on exactly what constitutes a valid combination, and be very specific and detailed about the "rules" that dictate valid vs. invalid. However, be advised that unless that filtering puts a fairly small limit on the number of possible elements that can be combined, it may still be for naught.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Attributes X Y Z
Material a. 100 3 300
Material b. 200 4 800
Material c. 500 6 3000
Material d. 250 5 1250
Filter :
X start range: end range :
Y start range : end range :
Z start range : end range :
If i set x range as 200 to 400
It can show combinations which can have x value between the set range. Others records not required
-
urbanromio2 (11/28/2016)
Attributes X Y ZMaterial a. 100 3 300
Material b. 200 4 800
Material c. 500 6 3000
Material d. 250 5 1250
Filter :
X start range: end range :
Y start range : end range :
Z start range : end range :
If i set x range as 200 to 400
It can show combinations which can have x value between the set range. Others records not required
Correct me if I'm wrong but isn't this a variation on the classical bin packing problem? If it is, then there are tried and tested solutions, possibly even performance comparisons.
Although documented, straightforward they ain't. Any one of the experts following this thread will likely have to spend a few hours on this problem.
So be nice. Provide as much information as you can. Don't skint. And don't forget, when it's all done and dusted, that someone in the community has volunteered to help you for free, has spent some hours of their time thinking hard for a solution to your problem - a solution which you may be some years away from understanding.
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply