데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

피봇 변환 – 데이터 행렬 변환

 

안녕하세요 강성욱 입니다.(www.sqltag.org, www.sqler.com, http://blog.naver.com/jevida)

이번 시간에는 [피벗 변환]에 대해서 알아 보도록 하겠습니다.

 

SSIS에서 [피벗 변환]은 테이블 데이터 뿐만 아니라 다양한 데이터 형태의 입력 데이터데 대해서도 피벗 변환을 수행 할 수 있습니다.

 

[SSMS]를 실행하여 오늘 실습에 사용할 데이터를 생성 합니다.

오늘 실습은 성별에 대하여 피봇을 진행 하도록 합니다.

BEGIN TRY

    drop table Member

END TRY BEGIN CATCH END CATCH

GO

 

create table Member(

ID nvarchaR(100),

Gender nvarchar(2),

Age int,

Cash int)

GO

 

INSERT INTO Member VALUES ('A', 'M', 35, 100)

INSERT INTO Member VALUES ('B', 'F', 25, 200)

INSERT INTO Member VALUES ('C', 'M', 25, 100)

GO

 

 

 

[BIDS를 실행]하여 [Integration Services 프로젝트]를 생성 합니다.

 

[제어 흐름] 탭에서 [데이터 흐름 태스크]를 드래그 앤 드롭으로 추가 합니다.

[피벗 변환] 이름을 지정하여 태스크를 생성 합니다.

 

[데이터 흐름]탭에서 그림과 같이 [원본 데이터(OLE DB 원본)]와 [대상 데이터(OLE DB 대상)]을 끌어다 놓습니다. 그 사이 [피벗]작업을 추가하여 데이터 흐름선을 연결 합니다.

 

[원본 데이터]에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

그림과 같이 편집 창이 나타나면[OLE DB연결 관리자]에서 원본 DB의 연결 관리자를 선택하고 해당 테이블을 선택하여 [미리보기]를 이용하여 정상적으로 데이터가 읽어오는지 확인 합니다.

(본 실습에서는 위에서 생성한 예제 데이터를 이용합니다.)

 

[피벗] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다,

 

아래 그림과 같이 [고급 피벗 편집기]창이 나타납니다.

[구성 요소 속성]탭에서는 피벗의 이름 및 LocalID를 지정할 수 있습니다. 또한 유효성 체크 유무를 변경 할 수 있습니다.

 

[입력열] 탭에서는 피벗에 사용할 입력 열을 선택 합니다.

 

[입/출력] 속성 탭에서는 입력 받은 데이터를 피벗 데이터로 출력할 것인지 설정 합니다.

이때 주의 사항이 아래 그림처럼 입력 열에 대한 [LeneageID]값이 매핑 값이 됩니다.(중요함)

또한 [PivotUsage]값에 따라 행 및 열, 값 속성으로 분류 됩니다.

PivotUsage값

설명

0

열이 피벗 연산에 참여하지 않고 바로 출력

1

행 속성을 가지는 열

2

이 열의 값이 열의 속성을 가짐.(컬럼으로 갈 속성)

3

값 속성을 가지는열.

 

실습에서는

ID : 0

Gender : 2

Age : 1

Cash : 3

으로 PivotUsage를 지정 하였습니다.

 

[출력 열]에서는 [열 추가] 버튼을 이용하여 [출력 열]을 생성 합니다.

여기서 주의할 점이 출력 열에서 나타낼 데이터의 입력열을 지정해 주어야 하는데 이때 사용하는 것이 위에서 설명한 [LeneageID]값 입니다. [SourceColumn]값에 입력 열의 [LeneageID]을 입력하면 [DataType] 타입 또한 입력열의 데이터 타입으로 자동으로 변경 됩니다.

 

오늘 실습에서는 성별에 대한 피봇을 진행 합니다. 여기서도 주의할 점이 값에 대한(PivoUsage : 3) [LeneageID]를 입력해야 합니다. 그리고 필터 조건으로 [PivotKeyValue] 항목에 (PivotUsage : 2) 성별에 대한 값을 입력해 주어야 합니다.

 

 

  • ComprasionFlags – 그룹핑 작업을 수행 할 때 문자열에 대한 비교 처리 방법, 대소문자 구분이나 문자 너비, 기호 무시등의 문자열 비교 속성을 설정할 수 있습니다.
  • PivotKeyValue – SQL 쿼리에서 [CASE WHEN 컬럼명 = '조건값' THEN…] 형태에서 '조건 값'에 해당하는 값을 지정합니다.
  • SourceColumn – 연산을 수행할 값의 [LeneageID]값 입니다.

 

 

[대상 데이터] 작업에서 마우스 오른쪽을 클릭하여 [편집]을 선택 합니다.

 

[OLE DB 연결 관리자]에서 대상 DB의 연결 관리자를 선택 합니다. 그리고 [새로 만들기] 버튼을 클릭하여 대상 테이블을 새로 만들어 봅니다. (이미 대상 테이블이 존재하는 경우 [테이블 또는 뷰 이름]에서 대상 테이블을 선택 합니다.)

[새로 만들기]를 선택하면 피봇 데이터 형태의 컬럼이 생성 됩니다.

 

[매핑] 탭으로 이동하여 대상이 적절히 매핑 되었는지 확인 합니다.

 

편집이 완료 되었으면 패키지를 실행 합니다.

늘 그러듯!

녹색 : 정상 실행

빨간색 : 오류 발생

 

패키지 작업이 완료 되었으면 SSMS를 실행하여 데이터를 확인 합니다.

 

데이터를 확인하면 성별에 따라 Cash 값을 피봇된 것을 확인 할 수 있습니다.

 

이 작업은 SSIS를 이용하여 여러곳에서 데이터를 가져올 때 피벗 작업을 진행하여 데이터를 적재할 때 매우 유용할 듯 합니다. (통계 쿼리 대신 사용할까 고민 중입니다.) 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp



No. Subject Author Date Views
1533 SSIS - 검사점 jevida(강성욱) 2013.04.19 8352
1532 SSIS - 패키지 구성 [1] jevida(강성욱) 2013.04.19 10120
1531 SSIS - 프로세스 실행 태스크 [2] jevida(강성욱) 2013.04.19 10350
1530 SSIS - 변수 – 다양한 사용자 변수를 만들어 보자 [3] jevida(강성욱) 2013.04.19 10802
1529 SQL Server Version별 Sample Database Download 경로 [2] 쓸만한게없네(윤선식) 2013.04.17 6810
1528 InstrCount함수와 InstrCountRev함수를 한번 만들어봤습니다. [5] Light 2013.03.27 6094
1527 구분자에 의해 구분되어 반환되는 split함수가 없어서 한번 만들어 봤습니다. [2] Light 2013.03.27 7537
1526 Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 42137
1525 Online restore [1] jevida(강성욱) 2013.03.15 6308
1524 SQL Server User Connections [1] jevida(강성욱) 2013.03.15 10597
1523 스크립트 구성 요소 – [데이터 대상] 사용 [1] jevida(강성욱) 2013.03.13 6763
1522 스크립트 구성 요소 – [데이터 변환] 사용 jevida(강성욱) 2013.03.13 6128
1521 SSIS - 스크립트 구성 요소 – [데이터 원본] 사용 jevida(강성욱) 2013.03.12 7832
1520 SSIS - 피벗 해제 변환 - 피봇된 데이터를 테이블 형태로 변환 하자! jevida(강성욱) 2013.03.12 7199
1519 SQL Server 단일 사용자 설정 시 SSMS로 로그인하기 [1] 쓸만한게없네(윤선식) 2013.02.25 12284
1518 SELECT 가 Deadlock? [2] 이스트럭(강동운) 2013.02.16 11693
1517 한글 초성 관련 [4] 건우아빠 2012.12.26 13133
1516 .NET 4.5 (VS2012) 설치 후 SSMS 에서 원격 MSSQL 2008 등이 접속 안될 경우! [2] 컴포지트 2012.12.11 17398
1515 SQL프로시저들의 매개변수를 한번에 샥 보여줍니다 [3] 차주언 2012.12.10 9268
» SSIS - 피봇 변환 - 데이터를 행렬 변환 하자! jevida(강성욱) 2012.11.30 10004





XE Login