#title 재귀쿼리를 이용한 Dumy테이블 만들기
[[TableOfContents]]

==== Hello World ====
{{{ cpp
#include <stdio.h>
int main()
{
        printf("Hello World!!\n");
        return 0;
}
}}}

==== 1부터10까지생성 ====
{{{
WITH Dumy(Seq)
AS
(
        SELECT 0 Seq
        UNION ALL
        SELECT Seq + 1 FROM Dumy
        WHERE Seq + 1 <= 10
)
SELECT Seq FROM Dumy
OPTION (MAXRECURSION 0);
--재귀횟수가 100을 초과할 경우는 OPTION (MAXRECURSION 0)를 붙여줘야 함.

with dumy
as
(
        select convert(binary(1), 0x01) bin
        union all
        select convert(binary(1), bin + 1) from dumy
        where bin + 1 <= 0xff
)
select bin, convert(tinyint, bin) num from dumy
option (maxrecursion 0);
}}}

==== 2000년01월01일~ 2000년12월31일까지만들기 ====
{{{
WITH temp(DT)
AS
(
        SELECT CONVERT(datetime, '20000101') DT 
        UNION ALL
        SELECT DT + 1 FROM temp
        WHERE DT + 1 < '20010101'
)
SELECT DT FROM temp
OPTION (MAXRECURSION 0);
--재귀횟수가 100을 초과할 경우는 OPTION (MAXRECURSION 0)를 붙여줘야 함.
}}}

==== 달력 ====
{{{
--누가 화면에서 달력 넣고 싶다고 조넨 귀찮게해서 만든거다.. ㅡㅡ;
DECLARE @DT datetime;
SET @DT = GETDATE();

WITH temp(DT)
AS
(
        SELECT DATEADD(mm, -2, @DT) DT 
        UNION ALL
        SELECT DT + 1 FROM temp
        WHERE DT + 1 < DATEADD(mm, 2, @DT)
)
SELECT
	MIN(YY) YY
,	MIN(MM) MM
,	ISNULL(MIN(CASE WHEN WD = 1 THEN DD END), '') 일
,	ISNULL(MIN(CASE WHEN WD = 2 THEN DD END), '') 월
,	ISNULL(MIN(CASE WHEN WD = 3 THEN DD END), '') 화
,	ISNULL(MIN(CASE WHEN WD = 4 THEN DD END), '') 수
,	ISNULL(MIN(CASE WHEN WD = 5 THEN DD END), '') 목
,	ISNULL(MIN(CASE WHEN WD = 6 THEN DD END), '') 금
,	ISNULL(MIN(CASE WHEN WD = 7 THEN DD END), '') 토
FROM (
	SELECT 
		DT 
	,	CONVERT(varchar, DATEPART(dd, DT)) DD
	,	DATEPART(mm, DT) MM
	,	DATEPART(yy, DT) YY
	,	DATEPART(weekday, DT) WD
	,	DATEPART(ww, DT) WW
	FROM temp
	WHERE DATEPART(mm, DT) = DATEPART(mm, @DT) 
--		IN 
--		(
--			DATEPART(mm, DATEADD(mm, -1, GETDATE()))
--		,	DATEPART(mm, DATEADD(mm, 1, GETDATE()))
--		,	DATEPART(mm, DATEADD(mm, 0, GETDATE()))
--		)
) T
GROUP BY
	WW, MM
ORDER BY 1, 2
OPTION (MAXRECURSION 0); 
}}}
==== 문자열(가로) -> 문자열(세로) ====
{{{ Sql
Declare
	@Str VarChar(max);
Select
	@Str='This is a test...';

With Parse as
(
	Select
		SubString(@Str,1,1)[Chr],
		1[Idx]
	Union All
	Select
		SubString(@Str,Idx+1,1),
		Idx+1
	from Parse
	where (Idx+1)<=Len(@Str)
)
Select
	*
from Parse
option (MaxRecursion 0);
}}}

==== 주민번호7자리로 나이 테이블 만들기 ====
{{{ Sql
WITH temp(DT)
AS
(
        SELECT CONVERT(datetime, '18000101') DT 
        UNION ALL
        SELECT DT + 1 FROM temp
        WHERE DT + 1 < CONVERT(char(8), GETDATE(), 112)
)
SELECT
	DT
,	Birth7
,	CASE WHEN Age < 0 THEN 0 ELSE Age END -
	CASE WHEN SUBSTRING(Birth7, 3, 4) >= SUBSTRING(CONVERT(char(8), GETDATE(), 112), 5, 4) THEN 1 ELSE 0 END Age
FROM (
	SELECT 
		DT
	,	CASE 
			WHEN DT >= '20000101' THEN RIGHT(CONVERT(char(8), DT, 112), 6) + CONVERT(char(1), T.Gender + 2)
			WHEN DT <  '19000101' THEN RIGHT(CONVERT(char(8), DT, 112), 6) + CONVERT(char(1), RIGHT(T.Gender + 8, 1))
			ELSE RIGHT(CONVERT(char(8), DT, 112), 6) + CONVERT(char(1), T.Gender + 0)
		END Birth7
	,	DATEDIFF(yy, DT, CONVERT(char(8), GETDATE()-1, 112)) Age
	FROM temp
		CROSS JOIN (SELECT 1 Gender UNION ALL SELECT 2) T
) T
OPTION (MAXRECURSION 0);
}}}