SQL 사용자 Tip & 강좌
/*
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 )


