-1
declare @set varchar(50)
set @set = '1,4,2'

output:- 

    1,1,1  
    1,2,1  
    1,3,1  
    1,4,1  
    1,1,2  
    1,2,2       
    1,3,2  
    1,4,2
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
Pratik Sharma
  • 55
  • 6

2 Answers2

2

Here is one trick using Number table and Parsename function 

DECLARE @set varchar(50)
SET @set = replace(reverse('1,4,2'),',','.')

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3),
    fst as (SELECT col1 = n FROM Tally where n <= parsename(@set,1)),
    scd as (SELECT col2 = n FROM Tally where n <= parsename(@set,2)),
    thd as (SELECT col3 = n FROM Tally where n <= parsename(@set,3))
SELECT *
FROM   fst
        LEFT JOIN scd ON 1 = 1
        LEFT JOIN thd ON 1 = 1
ORDER  BY col1,col3,col2

You can increase the numbers if required by adding stacked CTE with some more CROSS JOIN's

Result : 

╔══════╦══════╦══════╗
║ col1 ║ col2 ║ col3 ║
╠══════╬══════╬══════╣
║    1 ║    1 ║    1 ║
║    1 ║    2 ║    1 ║
║    1 ║    3 ║    1 ║
║    1 ║    4 ║    1 ║
║    1 ║    1 ║    2 ║
║    1 ║    2 ║    2 ║
║    1 ║    3 ║    2 ║
║    1 ║    4 ║    2 ║
╚══════╩══════╩══════╝

For more info on Numbers table

Tally Tables in T-SQL

What is the best way to create and populate a numbers table?

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • thansk for your answer. but it is not generating the series as expected in output – Pratik Sharma Jun 22 '17 at 05:39
  • @PratikSharma - Added `order by` did you check the latest code. Updated with Output – Pரதீப் Jun 22 '17 at 05:40
  • thanks buddy it is working great as expected. can you please help me more as in another case the expected input string might be only '1,4'. In that situation it is not returning any result set. It would be really helpful if code can work on both types of input. – Pratik Sharma Jun 22 '17 at 05:51
1

Here's a solution that uses a recursive CTE to build the Tally table with the numbers.
So that the maximum number isn't a fixed thing.

It then joins the numbers from the set to them.

And a concat is used to generate the sets.

DECLARE @set varchar(10);
SET @set = '1,4,2';

;WITH S AS
( 
  select 
  cast(parsename(revset,1)as int) as n1,
  cast(parsename(revset,2)as int) as n2,
  cast(parsename(revset,3)as int) as n3
  from (
     select replace(reverse(@set),',','.') revset
     ) q
),
N AS
(
   select 1 as n
   union all
   select n+1
   from N, S 
   where (N.n < S.n1 or N.n < S.n2 or N.n < S.n3)
)
select 
concat(nr1.n,',',nr2.n,','+cast(nr3.n as varchar(2))) as [Sets]
from S 
join N nr1 on (nr1.n <= S.n1)
join N nr2 on (nr2.n <= S.n2)
left join N nr3 on (nr3.n <= S.n3)
order by nr3.n, nr2.n, nr1.n;

Returns:

Sets
-----
1,1,1
1,2,1
1,3,1
1,4,1
1,1,2
1,2,2
1,3,2
1,4,2

It'll still work if the @set has only 2 numbers (note the left join on n3)

And if you're certain that those numbers don't go over 9 then you could simplify the query by replacing the recursive CTE by a list of values.
For example: 

N AS (select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
LukStorms
  • 28,916
  • 5
  • 31
  • 45