/*

drop table #numT

go

 

 

select *

into #numT

from (

select 1 num union all

select 2   union all

select 3   union all

select 4   union all

select 5   union all

select 6   union all

select 7   union all

select 8   union all

select 9   union all 

select 10   union all

select 11   union all

select 12   union all

select 13   union all

select 14   union all

select 15   union all

select 16   union all

select 17   union all

select 18   union all

select 19   union all

 

select 20   union all

select 21   union all

select 22   union all

select 23   union all

select 24   union all

select 25   union all

select 26   union all

select 27   union all

select 28   union all

select 29   union all

 

select 30   union all

select 31   union all

select 32   union all

select 33   union all

select 34   union all

select 35   union all

select 36   union all

select 37   union all

select 38   union all

select 39   

 

 ) a

*/

declare  @num int , @dan int

DECLARE  @SQL VARCHAR(MAX)

 

set  @num = 19   -- ( 이게몇단)

set  @dan = 3     --  한줄에입력한칸씩 2이면2단씩보여주줌.. 해놓고보니변수를잘못사용....

 

SELECT @SQL = 'SELECT '''' K'

 

SELECT @SQL = @SQL + ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = ' + CONVERT(VARCHAR,NUM) +' THEN A ELSE '''' END)  '+ 'A'+CONVERT(VARCHAR,NUM)+

       ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = '  + CONVERT(VARCHAR,NUM) +' THEN B ELSE '''' END)  '+ 'B'+ CONVERT(VARCHAR,NUM)+

       ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = '  + CONVERT(VARCHAR,NUM) +' THEN C ELSE '''' END)  '+ 'C'+CONVERT(VARCHAR,NUM) 

  FROM #numT WHERE NUM < (@dan  )

 

SELECT @SQL = @SQL + ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = 0 THEN A ELSE '''' END)  ' + 'A'+CONVERT(VARCHAR,@dan)  +

       ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = 0 THEN B ELSE '''' END)  '+ 'B'+CONVERT(VARCHAR,@dan)+

       ',MAX(CASE WHEN A % '+CONVERT(VARCHAR,@dan)+' = 0 THEN C ELSE '''' END)  '+ 'C'+CONVERT(VARCHAR,@dan)

 

 SELECT @SQL = @SQL + ' from (    select a.num  a '

 SELECT @SQL = @SQL + '                , b.num  b '

 SELECT @SQL = @SQL + '                , a.num * b.num  c '

 SELECT @SQL = @SQL + '  from #numT a cross join #numT b  '

 SELECT @SQL = @SQL + '  where a.num <= ' + CONVERT(VARCHAR,@num) +'  and b.num <= '+CONVERT(VARCHAR,@num)+' ) res '

 SELECT @SQL = @SQL + '    group by CEILING( a / ('+ CONVERT(VARCHAR,@dan)+' * 1.)) , b '

 SELECT @SQL = @SQL + '       order by CEILING( a / ('+   CONVERT(VARCHAR,@dan)+ ' * 1.)) , b '

 

SELECT @SQL

EXEC( @SQL )

 

 





profile