Power Set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Power Set

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-18 : 06:21:01

Input:


i
----
1
2
3
4

Result/Output

colName
----------------
1
2
3
4
1, 2
1, 3
1, 4
2, 3
2, 4
3, 4
1, 2, 3
1, 2, 4
1, 3, 4
2, 3, 4
1, 2, 3, 4
NULL

Table schema:


declare @t table(i int, unique(i))
insert @t select 1 union select 2 union select select 3 union select 4

______________________

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-05-18 : 11:28:20

select cast(i as varchar) from @t

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar)
from @t a
inner join @t b
on a.i < b.i

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar) + ', ' + cast(c.i as varchar)
from @t a
inner join @t b
on a.i < b.i
inner join @t c
on b.i < c.i

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar) + ', ' + cast(c.i as varchar) + ', ' + cast(d.i as varchar)
from @t a
inner join @t b
on a.i < b.i
inner join @t c
on b.i < c.i
inner join @t d
on c.i < d.i

union all

select null

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-18 : 11:48:15

Thanks.
It is not dynamic. my data rows are dynamic, it means I do not know how many rows existed in the table.
Just run this query to see the mistake.

declare @t table (i varchar(500), unique(i))
insert @t 
select 101 union 
select 202 union 
select 301 union 
select 40 union 
select 505 union 
select 888



select cast(i as varchar) from @t

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar)
from @t a 
inner join @t b 
on a.i < b.i

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar) + ', ' + cast(c.i as varchar)
from @t a 
inner join @t b 
on a.i < b.i
inner join @t c 
on b.i < c.i

union all

select cast(a.i as varchar) + ', ' + cast(b.i as varchar) + ', ' + cast(c.i as varchar) + ', ' + cast(d.i as varchar)
from @t a 
inner join @t b 
on a.i < b.i
inner join @t c 
on b.i < c.i
inner join @t d 
on c.i < d.i

union all

select null






/*
;WITH RecCTE 
AS(	SELECT Convert(VarChar(30),i) i 
      FROM @T	
   
    UNION ALL	
 
    SELECT Convert(Varchar(30), t2.i + ',' + t3.i) as i 
      FROM @T T2	
           CROSS JOIN RecCTE T3	
     WHERE t2.i <> t3.i 
       AND t2.i < LEFT(t3.i,1))

SELECT * 
FROM RecCTE 
ORDER BY LEN(I),i*/

______________________

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-05-18 : 14:21:38

Oh...

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources