sql-servert-sqlpowerset

powerset(all combinations) of a resultset in T-SQL


I need a t-sql code to get powerset of a resultset.

example input :

ColumnName
1
2
3

Example Output(one columns as nvarchar) :
1
2
3
1,2
1,3
2,3
1,2,3

Output set may contain duplicate values such as (1,3 vs 3,1).


Solution

  • You can use this one and get full powerset.
    Enjoy it.

    --EXEC PowerSet 'A,B,C,D'
    Create PROCEDURE PowerSet(@Members NVARCHAR(64))
    As
    Begin
        Declare @SubSet NVARCHAR(Max),
                @SubSetCount int,
                @Counter1 int,
                @Counter2 int,
                @ID int
        Create table #Members
        (
            ID int IDENTITY(1,1) PRIMARY KEY,
            Member NVARCHAR(50)
        )
        Create table #PowerSets
        (
            SubSet NVARCHAR(50)
        )
        -- Start Convert @Members To Table #Members
        Set @Members=@Members+','
        While @Members<>''
        Begin
            Insert Into #Members(Member) 
            Values(Left(@Members,CHARINDEX(',',@Members)-1))
            Set @Members=RIGHT(@Members,LEN(@Members)-CHARINDEX(',',@Members))
        End
        -- End Convert @Members To Table #Members
        -- Start Calculat PowerSet
        Set @SubSetCount=POWER(2,(Select Count(*) From #Members))
        Set @Counter1=0
        while @Counter1<@SubSetCount
        Begin
            Set @Counter2=@Counter1
            Set @ID=1
            Set @SubSet='{'
            While @Counter2>0
            Begin
                If CONVERT(char(1), @Counter2 % 2)=1
                     Set @SubSet=@SubSet+iif(@SubSet<>'{',',','')+(Select Member From #Members Where ID=@ID)
                Set @Counter2 = CONVERT(int, (@Counter2 / 2))
                Set @ID=@ID+1
            End
            Set @Counter1=@Counter1+1
            Insert into #PowerSets(SubSet) values(@SubSet+'}')
        End
        -- End Calculat PowerSet
        Select *
        From #PowerSets
        Order By Len(SubSet),SubSet
        Drop Table #PowerSets
        Drop Table #Members 
    End