공부하는 개발자

아래 쿼리로 db링크를 조회해 볼수 있다. 
SELECT * FROM master.dbo.sysservers 

bulk insert MTX_ANALYSIS_RESULT_MASTER_FI
from 'D:\devhdjoo_data.csv'
with(
FIELDTERMINATOR = ',' , --컬럼 구분 구호
ROWTERMINATOR = '\n' --row 구분 구호
)
Go

엑셀 파일을 저장시 확장자를 csv로 저장할것. !!!

아래처럼 한셀에 한 column씩 ''(작은따옴표) 없이  저장하고, 한 컬럼마다 ,(콤마)로 구분하고, 레코드단위로 줄바꿔  저장할것 !!!

1230, 30 ,01, 1, 1, 0201001002, 샘플일자 ,20130902

MSSQL은 VARCHAR TYPE의 컬럼에 숫자값이 들어 있을때   ORDER BY 가 원하는 순서대로 나오지 않을때가 있다. 

예를 들면 A라는 컬럼(VARCHAR)에 10,20,100,200이 들어있을경우, 해당 컬럼으로 ORDER BY 하면 10,100,20,200으로 정렬 된다. VARCHAR이므로 젤 앞글자로 우선 ORDER BY 되기 때문이다.

이럴때는  임의로 CONVERT(INT,A)해서 ORDER BY를 하면 제대로 정렬된다. 

---참고사항: MAX가 제대로 안될때도 CONVERT(INT,A)이용하자. 

쿼리를 짜다보면 트리 구조 형태로 데이터를 만들어야 하는 경우가 종종있다. 이를 경우 MSSQL에서 사용할 수 있는 방법이 있다. 바로 재귀 쿼리 이다. 재귀적 CTE의 기본 형식은 다음과 같다. 

WITH CTE_RECURSIVE(column_name[,...n]) AS
(
    <쿼리문1: SELECT * FROM TABLE_A // Anchor member
    UNION ALL
    <쿼리문2: SELECT * FROM TABLE_A JOIN CTE_테이블 이름 // Recursive member
)
-- Statement using the CTE --
SELECT * FROM CTE_RECURSIVE

재귀 실행의 의미 체계는 다음과 같습니다.

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
2. 앵커 멤버를 실행(쿼리문1)하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버(쿼리문2)를 실행합니다.
4. 빈 집합이 반환될 때까지 3단계를 반복합니다.
5. 결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다.

지금부터 예제를 통해 적용해보자. 

아래는 트리구조로 만든 임시 테이블이다.  

WITH TEST(UPPER_CODE,CODE,NAME) AS (
	SELECT NULL,10, 'LEVEL1'
	UNION ALL 
	SELECT '10','1001','LEVEL2'
	UNION ALL
	SELECT NULL,20, 'LEVEL1'
	UNION ALL
	SELECT '20','2001','LEVEL2'
	UNION ALL 
	SELECT '2001','2001001','LEVLE3'
)
SELECT * FROM TEST

<실행결과>

UPPER_CODE      CODE          NAME
NULL                 10              CODE10 
10                     1001           CODE1001 
NULL                 20              CODE20 
20                    2001           CODE2001 
2001                 2001001      CODE2001001

CODE 2001001을 보자. UPPER_CODE 가 2001이다.  그럼 다시 CODE가 2001인 데이터는 UPPDER CODE가 20이고 ITEM CODE=20인 데이터는 UPPER_CODE가 NULL이다. 즉, 레벨  구조가 아래와 같다. 

20                                                                                                                                                         L  2001                                                                                                                                                       L2001001

그럼 재귀 쿼리를 이용하여 각 CODE의 레벨을 지정 해보자. (레벨 1: 1, 레벨2: 2, 레벨3: 3) 각 레벨에 숫자를 부여하는 쿼리와 실행 결과는 아래를 참고하기 바란다. 

실행 결과는 아래와 같다 

그럼 좀더 응용해보자.  총 레벨이 3단계 까지 있으므로 각 코드의 상위 레벨들의 CODE와 NAME을 한 ROW에 모두 나오도록 QUERY를 작성해보자. 

예를 들어, 20                                   의 경우                                                                                                             L  2001                                                                                                                                                       L2001001

CODE1 CODE1_NAME CODE2 CODE2_NAME    CODE3     CODE3_NAME   LEVEL
20        ITEM20          2001  ITEM2001         2001001      ITEM2001001     3

이렇게 출력 될 수 있도록 말이다. 그럼 우선, 위 쿼리 실행 결과를 뷰 테이블로 만들고 다시 재귀 쿼리를 이용해야 한다. Microsoft SQL Server Management Studio에서 View 테이블을 직접 만들어도 되지만, 여기서 공부하는 용으로 정리하는 것이니 WITH AS 을 이용하여 위 데이터를 가지는 임시 vm_table을 만들어 사용했다.

WITH  VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
SELECT NULL, '10', 'ITEM10', 1
UNION ALL 
SELECT NULL, '20' ,'ITEM20', 1
UNION ALL 
SELECT '20', '2001' ,'ITEM12001', 2
UNION ALL
SELECT '2001', '2001001', 'ITEM2001001', 3
UNION ALL
SELECT '10', '1001', 'ITEM101001', 2
UNION ALL
SELECT '1001', '1001001', 'ITEM101001', 3
) 
SELECT * FROM VM_CTE

이 뷰테이블 VM_CTE를 이용하여  작성한 쿼리와 실행 결과를 아래에 정리해두었다. 참고하기 바란다. 

WITH VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
    SELECT NULL, '10', 'ITEM10', 1
    UNION ALL
    SELECT NULL, '20' ,'ITEM20', 1
    UNION ALL
    SELECT '20', '2001' ,'ITEM2001', 2
    UNION ALL
    SELECT '2001', '2001001', 'ITEM2001001', 3
    UNION ALL
    SELECT '10', '1001', 'ITEM1001', 2
    UNION ALL
    SELECT '1001', '1001001', 'ITEM1001001', 3
) ,CTE_RESURSIVE AS (
    SELECT CODE, CODE CODE1,
          CASE WHEN 1=2 THEN CODE ELSE NULL END CODE2,
          CASE WHEN 1=2 THEN CODE ELSE NULL END CODE3,
          NAME NAME1,
          CASE WHEN 1=2 THEN NAME ELSE NULL END NAME2,
          CASE WHEN 1=2 THEN NAME ELSE NULL END NAME3,
          LEVEL
    FROM VM_CTE WHERE UPPER_CODE IS NULL
   
    UNION ALL
    SELECT SC.CODE, CC.CODE1 CODE1,
              CASE WHEN SC.LEVEL = 1 THEN NULL
                      WHEN SC.LEVEL = 2 THEN SC.CODE
                      WHEN SC.LEVEL = 3 THEN CC.CODE2 END CODE2,
              CASE WHEN SC.LEVEL = 1 THEN NULL
                      WHEN SC.LEVEL = 2 THEN NULL
                      WHEN SC.LEVEL = 3 THEN SC.CODE END CODE3 ,
              CC.NAME1 NAME1,
              CASE WHEN SC.LEVEL = 1 THEN NULL
                      WHEN SC.LEVEL = 2 THEN SC.NAME
                      WHEN SC.LEVEL = 3 THEN CC.NAME2 END NAME2,
              CASE WHEN SC.LEVEL = 1 THEN NULL
                      WHEN SC.LEVEL = 2 THEN NULL
                      WHEN SC.LEVEL = 3 THEN SC.NAME END NAME3 ,
             SC.LEVEL
    FROM VM_CTE SC INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE
    WHERE UPPER_CODE IS NOT NULL ) SELECT * FROM CTE_RESURSIVE

WITH  VM_CTE(UPPER_CODE,CODE,NAME,LEVEL) AS (
	SELECT NULL,	'10',	'ITEM10',	1
	UNION ALL 
	SELECT NULL,	'20'	,'ITEM20',	1
	UNION ALL 
	SELECT '20',	'2001'	,'ITEM2001',	2
	UNION ALL
	SELECT '2001',	'2001001',	'ITEM2001001',	3
	UNION ALL
	SELECT '10',	'1001',	'ITEM1001',	2
	UNION ALL
	SELECT '1001',	'1001001',	'ITEM1001001',	3
) ,CTE_RESURSIVE AS (
	SELECT CODE, 
	            CODE  CODE1,
				CASE WHEN 1=2 THEN CODE
						 ELSE NULL END CODE2, 
				CASE WHEN 1=2 THEN CODE
						 ELSE NULL END CODE3, 
				NAME NAME1,
				CASE WHEN 1=2 THEN NAME
						 ELSE NULL END NAME2, 
				CASE WHEN 1=2 THEN NAME
						 ELSE NULL END NAME3, 
				LEVEL
	FROM VM_CTE
	WHERE UPPER_CODE IS NULL

	UNION ALL
	SELECT SC.CODE, 
				CC.CODE1 CODE1, 
				CASE WHEN SC.LEVEL=1  THEN NULL
						 WHEN SC.LEVEL=2  THEN SC.CODE
						 WHEN SC.LEVEL=3 THEN  	CC.CODE2	
				END CODE2, 
				CASE WHEN SC.LEVEL= 1 THEN NULL 	
						WHEN SC.LEVEL = 2 THEN NULL
						WHEN SC.LEVEL = 3 THEN SC.CODE
				END CODE3 , 
				CC.NAME1 NAME1,
				CASE WHEN SC.LEVEL=1  THEN NULL
						 WHEN SC.LEVEL=2  THEN SC.NAME
						 WHEN SC.LEVEL=3  THEN CC.NAME2			
				END NAME2, 
				CASE WHEN SC.LEVEL= 1 THEN NULL 	
						WHEN SC.LEVEL = 2 THEN NULL
						WHEN SC.LEVEL = 3 THEN SC.NAME
				END NAME3 ,
				SC.LEVEL 
	FROM VM_CTE SC
	INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE
	WHERE UPPER_CODE IS NOT NULL
)
SELECT *
FROM CTE_RESURSIVE

<<실행결과 >>

위 쿼리를 잠깐 살펴보자. 

재귀 쿼리는 Anchor 멤버를 먼저 실행후 ,T0을 만든다고 했다. 그래서 위 쿼리의 Anchor멤버를 실행하게 되면 T0가 생성이 된다. 

T0

T1

T0가 입력으로 사용되어 Recursive멤버가 실행되게 된다. 위 쿼리에서 Recursive 멤버인 아래 쿼리가 실행이 되는 것이다. 

SELECT  SC.CODE,  
......생략......
SC.LEVEL  
FROM VM_CTE SC 
INNER JOIN CTE_RESURSIVE CC ON SC.UPPER_CODE=CC.CODE 
WHERE UPPER_CODE IS NOT NULL

그럼 T0은 CC 로 Alias된 부분이 되고 SC로 ALIAS된 Table과 Inner Join하게 되는 것이다. 즉, T0의 CODE가 SC의 UPPER_CODE인 Record를 찾게된다. 그 결과가 T1이 된다. 

T2

위에서 생성된 T1이 다시 입력으로 들어가서 SC로 Alias된 Table과 Inner Join하게 되고 그 결과로 T2가 만들어진다. 

T3

위에서 생성된 T2가 다시 입력으로 들어가서 SC로 Alias된 Table과 Inner Join하게 되고 그 결과로 T3가 만들어진다. 

그런데 그 결과 Record가 존재 하지 않는다. 그래서 T3는 만들어 지지 않고  재귀 호출은 여기서 종료 된다. 

T1,T2,T3 UNION ALL

재귀 호출이 종료된 후 지금 까지 생성된 T0,T1,T2 가 UNION ALL되어 출력되게 된다.

처음 재귀 쿼리를 접했을때 이 내용을 이해하기 까지 시간이 오래 걸렸다. 나 처럼 재귀쿼리 첨 접하는 사람에게 조금이나마 이해하는데 도움이 되었으면 좋겠다... 

쿼리를 작성하다 보면 숫자와 문자가 뒤섞인 데이터들이 많이 있다. 이런 데이터들을 order by 하면 내가 원하는 순서대로 오름차순 또는 내림차순이 되지 않는 경우가 있다. 예를 들어 아래 쿼리를 보자. 내림차순 정렬을 했지만, 출력되는 순서는 5S,11S,10S인걸 알수 있다.  이처럼 데이터 정렬이 제대로 안될경우 해결 방법이 있다. 

이를 경우는 order by Len( column_Name) 을 사용하면 된다. 아래처럼 원하는 순서로 출력되는 것을 볼 수 있다. 

여러행의 특정 컬럼 값을 한줄로 나타내 주고 싶을때가 있다. 

이럴때 이용할수 있는 방법이 있어 정리해둔다. 

학년,반,점수 컬럼을 가지는 임의의 테이블을 하나 만들었다. 

WITH TABLE_A( 학년, 반,점수) AS (
    SELECT '1학년','1반',100 UNION ALL
    SELECT '2학년','1반',200 UNION ALL
    SELECT '3학년','1반',300 
)
SELECT * FROM TABLE_A

나는 아래와 같이 1학년,2학년,3학년 이렇게 한줄로 보고 싶었다.

그럴땐 FOR XML PATH 을 이용하면 된다.  

그런데 1학년 앞에 , 도 없애고 싶다. 이럴때 STUFF함수를 사용하면 된다. 

STUFF사용 방법은 아래와 같다. start 부터 크기까지 지정한 치환문자로 바꿔준다. 

STUFF('[문자열]','[시작위치]','[크기]','[치환문자]')