Complex variable length combinations of a string

Hi,
Could anyone help me with the following complex problem:
Suppose I have the following input: 'sig, ltw, onss'
Than my output should give the following result set:
sig
ltw
onss
sig, ltw
sig, onss
ltw, onss
sig, ltw, onss
The solution should also work with any given number of names in my comma
seperated string ex.:
input = sig, ltw, onss, pkl
output =
sig
ltw
onss
pkl
sig, ltw
sig, onss
sig, pkl
ltw, onss
ltw, pkl
onss, pkl
sig, ltw, onss
sig, onss, pkl
ltw, onss, pkl
sig, ltw, onss, pkl
Anyone an idea how to resolve this with T-SQL?
Thanx...Peter,
Took me some time to figure this out:
set nocount on
declare @.str as varchar(8000)
,@.count as int
,@.out as varchar(8000)
create table #str
(i int identity(0, 1)
, el varchar(4000))
set @.str='sig, ltw, onss'
--extract elements into a table
set @.str = @.str + ','
while @.str <> '' begin
insert into #str (el)
values (substring(@.str, 1, charindex(',', @.str) - 1))
set @.str = ltrim(substring(@.str, charindex(',', @.str) + 1, 8000))
end
--calculate number of combinations
select @.count = power(2, count(*)) - 1 from #str
--create all combinations
--each element as a binary position where
--1 means element is in and 0 means element is not in a given combination
while @.count > 0 begin
set @.out = ''
select @.out = @.out + el + ', '
from #str
where @.count & power(2, i) = power(2, i)
order by i
select left(@.out, len(@.out) - 1)
set @.count = @.count - 1
end
drop table #str
Ilya
"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:A2E5AF3D-4AEF-4E79-9395-639513E06D4E@.microsoft.com...
> Hi,
> Could anyone help me with the following complex problem:
> Suppose I have the following input: 'sig, ltw, onss'
> Than my output should give the following result set:
> sig
> ltw
> onss
> sig, ltw
> sig, onss
> ltw, onss
> sig, ltw, onss
> The solution should also work with any given number of names in my comma
> seperated string ex.:
> input = sig, ltw, onss, pkl
> output =
> sig
> ltw
> onss
> pkl
> sig, ltw
> sig, onss
> sig, pkl
> ltw, onss
> ltw, pkl
> onss, pkl
> sig, ltw, onss
> sig, onss, pkl
> ltw, onss, pkl
> sig, ltw, onss, pkl
> Anyone an idea how to resolve this with T-SQL?
> Thanx...
>|||Ilya, Thanks for your effort in resolving this; but look at the post just
before (Oops I sended it twice) the answer of David Portas is such a elegant
solution, you have to see it to belief it :-)
Thanx
"Ilya Margolin" wrote:

> Peter,
> Took me some time to figure this out:
> set nocount on
> declare @.str as varchar(8000)
> ,@.count as int
> ,@.out as varchar(8000)
> create table #str
> (i int identity(0, 1)
> , el varchar(4000))
> set @.str='sig, ltw, onss'
> --extract elements into a table
> set @.str = @.str + ','
> while @.str <> '' begin
> insert into #str (el)
> values (substring(@.str, 1, charindex(',', @.str) - 1))
> set @.str = ltrim(substring(@.str, charindex(',', @.str) + 1, 8000))
> end
> --calculate number of combinations
> select @.count = power(2, count(*)) - 1 from #str
> --create all combinations
> --each element as a binary position where
> --1 means element is in and 0 means element is not in a given combination
> while @.count > 0 begin
> set @.out = ''
> select @.out = @.out + el + ', '
> from #str
> where @.count & power(2, i) = power(2, i)
> order by i
> select left(@.out, len(@.out) - 1)
> set @.count = @.count - 1
> end
> drop table #str
> Ilya
>
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:A2E5AF3D-4AEF-4E79-9395-639513E06D4E@.microsoft.com...
>
>|||Peter,
That was my first thought. The downside of it you have to modify the
statement to add more joins as you add combination members, so the solution
is not scalable.
Ilya
"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> Ilya, Thanks for your effort in resolving this; but look at the post just
> before (Oops I sended it twice) the answer of David Portas is such a
elegant
> solution, you have to see it to belief it :-)
> Thanx
> "Ilya Margolin" wrote:
>
combination
comma|||You're right !
I was wondering how big the difference in performance is between the two
solutions? Have to try it out...
Thanx again :-)
"Ilya Margolin" wrote:

> Peter,
> That was my first thought. The downside of it you have to modify the
> statement to add more joins as you add combination members, so the solutio
n
> is not scalable.
> Ilya
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> elegant
> combination
> comma
>
>|||Ilya,
I had to write you back to say that your solution is by far the most elegant
of all.
I had to take some time to understand your solution but once I understood
it; I found it just brilliant.
Sorry for this late appreciation of your solution ;-)
Thanx again,
Peter
"Ilya Margolin" wrote:

> Peter,
> That was my first thought. The downside of it you have to modify the
> statement to add more joins as you add combination members, so the solutio
n
> is not scalable.
> Ilya
> "PeterM" <PeterM@.discussions.microsoft.com> wrote in message
> news:D5230490-26F4-4E72-915B-53688DFFC618@.microsoft.com...
> elegant
> combination
> comma
>
>