Generate Combination of numbers

Question

Post reply

Generate Combination of numbers

nchellaaraam

SSC Enthusiast

Points: 126
More actions
June 19, 2015 at 1:52 am

#297988

Hi,
I want to get "all combinations of the same count" for provided list of numbers.
As an example, if I have a string 'NA,T1,T1a'
For this string, I want to generate following:
NA,T1,T1a
NA,T1a,T1
T1,NA,T1a
T1,T1a,NA
T1a,NA,T1
T1a,T1,NA
Please help me out with the query to achieve the same or if the solution is already available, provide me with the links.
Thanks in advance.
Sorry for the misleading topic.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dohsan SSCrazy Points: 2972 More actions · Answer 1

If the provided list of numbers will always have the same number of items, then something like this should work.

DECLARE @Chuff VARCHAR(50) = 'NA,T1,T1a';

;WITH Test

AS

(

SELECTItemNumber,

Item

FROMdbo.DelimitedSplit8K(@Chuff,',')

)

SELECTT.Item,

T1.Item,

T2.Item

FROMTest AS T

CROSS

JOINTEST AS T1

CROSS

JOINTEST AS T2

WHERET.ItemNumber <> T1.ItemNumber

AND T1.ItemNumber <> T2.ItemNumber

and t.ItemNumber <> t2.ItemNumber

Artical referringto the string splitter can be found http://www.sqlservercentral.com/articles/Tally+Table/72993/

I have a feeling though you are after all combinations for a string with any number/varied amount of items?

nchellaaraam SSC Enthusiast Points: 126 More actions · Answer 2

Hi Dohsan,

Thanks for the quick response. The number of words will vary. Sorry that i did not mention this earlier.

The input string can be like 'NA,T1,T1a' or 'NA,T1,T1a,T2' or anything else. I need a to create a function that will return all the possible combinations of the input string. The input will be always as a single string containing multiple comma separated values.

Dohsan SSCrazy Points: 2972 More actions · Answer 3

Here's one using a recursive CTE, if I have time I'll have a go at a set based solution

DECLARE @Chuff VARCHAR(8000) = 'NA,T1,T1a'

DECLARE @SplitValues TABLE (Item VARCHAR(8000));

INSERT INTO @SplitValues (Item)

SELECTitem

FROMdbo.DelimitedSplit8K(@Chuff,',');

WITH rBase

AS

(

SELECTC.Item,

Combo = C.Item + ',',

N = 1

FROM@SplitValues AS C

UNION ALL

SELECTR.Item,

R.Combo + C1.Item + ',',

R.N + 1

FROM@SplitValues AS C1

INNER

JOINrBase AS R

ONR.Combo NOT LIKE '%' + C1.Item + ',%'

)

SELECTLEFT(R.Combo,CA1.ComboLen)

FROMrBase AS R

CROSS

APPLY(SELECT LEN(R.Combo)-1) AS CA1(ComboLen)

WHERER.N = (SELECT MAX(R1.N) FROM rBase AS R1 WHERE R1.Item = R.Item);

nchellaaraam SSC Enthusiast Points: 126 More actions · Answer 4

Hi Dohsan,

Thanks a lot for the solution. This is exactly what i wanted. The solution is working fine for 6 values beyond which it takes a lot of time to execute (For Eg. 'CR,CS,M,OTH,T1,T1b,T2,T3' ). Is there a way to speed up the process? Please help me out.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

Dohsan (6/19/2015)
Here's one using a recursive CTE, if I have time I'll have a go at a set based solution

This will not produce correct result for the input like 'NA,T1,aT1'. A minor tweak will help.

DECLARE @Chuff VARCHAR(8000) = 'NA,T1,aT1'

DECLARE @SplitValues TABLE (Item VARCHAR(8000));

INSERT INTO @SplitValues (Item)

SELECTitem

FROMdbo.DelimitedSplit8K(@Chuff,',');

WITH rBase

AS

(

SELECTC.Item,

Combo = ',' + C.Item + ',',

N = 1

FROM@SplitValues AS C

UNION ALL

SELECTR.Item,

R.Combo + C1.Item + ',',

R.N + 1

FROM@SplitValues AS C1

INNER

JOINrBase AS R

ONR.Combo NOT LIKE '%,' + C1.Item + ',%'

)

SELECTSTUFF(LEFT(R.Combo,CA1.ComboLen), 1, 1, '')

FROMrBase AS R

CROSS

APPLY(SELECT LEN(R.Combo)-1) AS CA1(ComboLen)

WHERER.N = (SELECT MAX(R1.N) FROM rBase AS R1 WHERE R1.Item = R.Item);

Dohsan SSCrazy Points: 2972 More actions · Answer 6

serg-52 (6/19/2015)
Dohsan (6/19/2015)
Here's one using a recursive CTE, if I have time I'll have a go at a set based solution
This will not produce correct result for the input like 'NA,T1,aT1'. A minor tweak will help.
DECLARE @Chuff VARCHAR(8000) = 'NA,T1,aT1'
DECLARE @SplitValues TABLE (Item VARCHAR(8000));
INSERT INTO @SplitValues (Item)
SELECTitem
FROMdbo.DelimitedSplit8K(@Chuff,',');
WITH rBase
AS
(
SELECTC.Item,
Combo = ',' + C.Item + ',',
N = 1
FROM@SplitValues AS C
UNION ALL
SELECTR.Item,
R.Combo + C1.Item + ',',
R.N + 1
FROM@SplitValues AS C1
INNER
JOINrBase AS R
ONR.Combo NOT LIKE '%,' + C1.Item + ',%'
)
SELECTSTUFF(LEFT(R.Combo,CA1.ComboLen), 1, 1, '')
FROMrBase AS R
CROSS
APPLY(SELECT LEN(R.Combo)-1) AS CA1(ComboLen)
WHERER.N = (SELECT MAX(R1.N) FROM rBase AS R1 WHERE R1.Item = R.Item);

aha good catch there Serg

Hi Dohsan,
Thanks a lot for the solution. This is exactly what i wanted. The solution is working fine for 6 values beyond which it takes a lot of time to execute (For Eg. 'CR,CS,M,OTH,T1,T1b,T2,T3' ). Is there a way to speed up the process? Please help me out.

I had a feeling this may be the case, the main issue with recursion is the more levels you go the more the performance is going to suffer. What is the most items you're expecting to go through? I'm sure there must be a more set based approach. If i have more time I'll give it a bash, that and I'm sure there are several on this forum who would be able to help.

Dohsan SSCrazy Points: 2972 More actions · Answer 7

Dohsan

SSCrazy

Points: 2972

June 19, 2015 at 8:00 am

#1806689

double post