Binaryworld - How to generate combinations of values in SQL Server? ... [ SQL Server -> T-SQL ]

Demonstrates the application of the CUBE operator for SELECT statement.

/*
    A temporary table is used to store the values for which the
    combinations have to be generated.
    Using a derived table in gives incorrect results & hence
    the workaround.
*/
declare @Value1 char( 1 ) , @Value2 char( 1 ) , @Value3 char( 1 ) , @Value4 char( 1 )
select @Value1 = 'a' , @Value2 = 'b' , @Value3 = 'c' , @Value4 = 'd'
create table #l (
    c1 char( 1 ) , c2 char( 1 ) , c3 char( 1 ) , c4 char( 1 )
);
insert #l values( @Value1 , @Value2 , @Value3 , @Value4 );
go
select coalesce( c1 , '' ) + coalesce( c2 , '' ) +
    coalesce( c3 , '' ) + coalesce( c4 , '' ) AS Combtn
from (
    select c1 , c2 , c3 , c4
    from #l
    group by c1 , c2 , c3 , c4
    with cube
) as t
where coalesce( c1 , c2, c3, c4 ) is not null
order by Combtn;
-- Expected Output:
/*
Combtn
------
a
ab
abc
abcd
abd
ac
acd
ad
b
bc
bcd
bd
c
cd
d
*/
go
drop table #l;

-- SQL2000 Only:
-- The derived table approach for completeness using the values directly
select coalesce( c1 , '' ) + coalesce( c2 , '' ) +
    coalesce( c3 , '' ) + coalesce( c4 , '' ) AS Combtn
from (
    select c1 , c2 , c3 , c4
    from (
        select 'a' , 'b' , 'c' , 'd'
    ) As c( c1 , c2 , c3 , c4 )
    group by c1 , c2 , c3 , c4
    with cube
) as t
where coalesce( c1 , c2, c3, c4 ) is not null;
go