/*
        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

This page was last updated on May 01, 2006 04:28 PM.