i Want to genrate possible combination with particluar no in sql server 2005 recursively.
e.g
we Have Main No 2 and Sub no 4,5,6 Combination are
2 4
2 5
2 6
4 5
4 6
5 6
5 2
...........like开发者_开发百科 combination.
Thank u
declare @T table (Num int)
insert into @T values (2)
insert into @T values (4)
insert into @T values (5)
insert into @T values (6)
select
T1.Num as Num1,
T2.Num as Num2
from @T as T1
cross join @T as T2
Insert the numbers in TempTable and use Cross Join
Create Table #LeftTemp
( ID int,
)
Create Table #RightTemp
( ID int,
)
-- Write insert statements
Select * from #LeftTemp CROSS JOIN #RightTemp
;With
List AS (SELECT 2 AS y UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)
SELECT
*
FROM
List CROSS JOIN list
You'd generate list from your tables
精彩评论