Row형식의 Data를 Column형식으로 변환 및 역으로 변환 T-SQL




1. Row형식의 Data를 Column형식으로 변환 
 쿼리좀 다루신 분들은 다들 아는 내용입니다. Case문으로 간단히 처리가 가능하지요.
다음과 같은 예제 코드를 가지고 설명을 하자면 Key값이 월로 생각하고 1월,의 order,sales,inventory를 컬럼으로 변환새서 보고자 할 때 자주 사용되는 패턴이죠

DECLARE @tblMaster Table(Key1 INT)
DECLARE @tblDtl  Table(Key1 INT,classify varchar(10), Value1 INT)

insert into @tblMaster values(1)
insert into @tblDtl values(1,'order',1)
insert into @tblDtl values(1,'sales',2)
insert into @tblDtl values(1,'inventory',3)
insert into @tblMaster values(2)
insert into @tblDtl values(2,'order',10)
insert into @tblDtl values(2,'sales',233)
insert into @tblDtl values(2,'inventory',143)

select  A.Key1
  , SUM(CASE WHEN B.classify = 'order' Then B.Value1 Else 0 END) morder
  , SUM(CASE WHEN B.classify = 'sales' Then B.Value1 Else 0 END) sales
  , SUM(CASE WHEN B.classify = 'inventory' Then B.Value1 Else 0 END) inventory
from @tblMaster a
  inner join @tblDtl b on a.Key1 = B.Key1
group by A.Key1

책같은데 봐도 이런 예는 많고 실재로 업무에서도 많이 사용됩니다. 하지만 위의 쿼리는 유연성 면에서 문제점이 있습니다.
문제1) order,sales,inventory라는 구분값들이 변경이 될때가 직접 쿼리를 수정 해줘야 하는 문제점이 있습니다.
        (뭐.. 그때 그떄 수정 해 주면 돼지~ 라고생각하시는 분은 시스템분석/설계 하는 일은 하시면 안됩니다. 민폐를 끼치게 되는 일이 발생 합니다.) 
해결1) 다음과 같이 분류 테이블을 만들어서 분류의 키값으로 조회하는 방식을 사용하면 됩니다.
 코드값이 바끼면 어떻게 할거냐? 라고 생각하시는 분도 있겠지만 코드값이 바뀌면 모든 DB는 그 코드값으로 일괄 변경시켜줘야 한다는거~~
 그리고 여기에서 보듯이 Table설계시에 테이블을 정규화를 왜 해줘야 하는 것 또한 이해 할 수 이을것입니다. 분류를 따로 저장하는 테이블이 없을 경우에는 유연성을 생각 할 수도 없다는거~~~
DECLARE @tblClassify Table(clas_Cd INT, classify_Nm varchar(10))
insert into @tblClassify values(100,'order')
insert into @tblClassify values(101,'sales')
insert into @tblClassify values(102,'inventory')

DECLARE @tblMaster Table(Key1 INT)
DECLARE @tblDtl  Table(Key1 INT,clas_Cd INT, Value1 INT)

insert into @tblMaster values(1)
insert into @tblDtl values(1,100,1)
insert into @tblDtl values(1,101,2)
insert into @tblDtl values(1,102,3)
insert into @tblMaster values(2)
insert into @tblDtl values(2,100,10)
insert into @tblDtl values(2,101,233)
insert into @tblDtl values(2,102,143)

select  A.Key1
  , CASE B.clas_Cd WHEN (SELECT clas_Cd From @tblClassify where clas_cd = 100 ) Then B.Value1 Else 0 END morder
  , CASE B.clas_Cd WHEN (SELECT clas_Cd From @tblClassify where clas_cd = 101 ) Then B.Value1 Else 0 END sales
  , CASE B.clas_Cd WHEN (SELECT clas_Cd From @tblClassify where clas_cd = 102 ) Then B.Value1 Else 0 END inventory
from @tblMaster a
  inner join @tblDtl b on a.Key1 = B.Key1
물론 Sum을 한번 해줘야 이전 결과와 같이 됩니다. 한번더 Select를 둘러싸던지 임시테이블에 저장 해 두고 한번더 Select하던지 알아서 해 보시공~`

문제2) 결과 쿼리에서 분류의 명을 사용하고 싶은 경우입니다. 
위는 일일이 해당 분류명을 컬럼명으로 사용하게 막코딩을 해놨는데 필자의 경우 이런류의 쿼리를 짜면서 문자열이 테이블의 컬럼명에 해당하는 경우 컬럼으로 변환해주는 함수를 제공해줬으면 하는 바램이 생기더군요.
물론 도움말 찾아봐도 그런함수는 없습니다. 예를들자면 TableColumn(테이블명,'컬럼명')으로 적으면 해당 컬럼명으로 실행시 변환해주는 뭐.. 그런거 ㅎㅎㅎ
자바스크립트의 eval함수 같은것을 말하는데... 없다는거....
그래서 어쩔 수 없이 동적쿼리를 사용해야만 해결 할 수 있더군요..
문제3) 분류가 추가되거나 삭제 될 때 어떻게 유연하게 대처 할 수 있을까? 입니다.
문제2번과 밀접하게 관련이 있고 이것또한 동적쿼리를 작성해야만 가능한것으로 필자는 결론내렸습니다.
Pivot기능을 이용하여 동적 컬럼을 생성하는 방법도 있던데~ 차후에 필요하면 추가로 올리도록 하고 여기는 필자가 자주 사용하는 방식을 올립니다.

문제2의 경우는 동적쿼리로 작성하면 해당 컬럼명만 넣어주면되고 문제3의 경우는 루프를 한번 돌려야만 가능한 일일텐데.....
여기서 루프를 돌린다고 무조건 커스를 생각하시면 안되고~~ 단순히 문자열을 연결하는 경우에는 Select한번으로 할 수 있는 방법이 있습니다.
아래코드는 제가 실무에서 만들었던 코드의 일부 입니다. 2,3번문제 모두를 해결 할 수 있습니다.
여기서 간단히 Sql서버의 임시테이블에 대해 설명하고 넘어가면
위와같이 Declare @tblTable Table(....)형식으로 선언하면 코딩의 블럭 단위의 테이블이 되는것이고 #으로 선언된 아래의 코드의 경우는 현재 프로시져를 호출하는 셔센의 전역변수가 된다는거~~ 또한 ##Table로 선언하는 경우는 SqlSever전역에 해당하는 테이블로 사용된다는거~~ 
@와 #의 차이는 분명히 알아야 다음 부분코드를 이해 할 수 있습니다. 프로시져가 종료되기 전까지는 #테이블은 유효하다는거~~

우선 아래코드를 보면 week테이블(분류성테이블)을 생성하고 주차정보를 넣습니다. 
#으로 선언된 이유는 나중에 동적쿼리에서 이 테이블이 조인에 참여해서 입니다.
  CREATE TABLE  #tblWeek (SEQN INT IDENTITY( 1, 1),YYYYWW CHAR(7)) 

  INSERT INTO #tblWeek
  SELECT 'W'+ YYYYWW -- 이부분은 본인이 필드명을 사용하고 싶은데로 조합하면 됩니다.
  FROM dbo.fn_TimeLine(@OrgID)    -- 이분은 분류테이블이 오면되구
  WHERE YYYYWW BETWEEN @STARTWEEK AND @ENDWEEK    
  ORDER BY YYYYWW 

  -- 자.. 이쿼리 한방이면 내가 원하는 쿼리의 일부분을 얼마든지만들 수 있지요~ 위의 Case문을 만들고 싶으면 앞,뒤에 적어주면 그만이죠~
  SELECT @WeekStr =   @WeekStr  + YYYYWW  + ' INT'  + ','
  FROM #tblWeek 
  ORDER BY YYYYWW
 

자.. 이렇게 하면 내가 원하는 동적쿼리는 얼마든지 만들 수 있겠죠? 여기서 저 쿼리들을 만들고 뒤에 연결될 테이블과 조건까지 모두 동적쿼리로 만드어야만 쿼리를 실해하여 원하는 겨과를 얻을 수 있다고 생각 할 수도 있겠지만... 그렇게 하지 않아도 되는 방법이 있습니다.
이것은 나름 필자의 노하우입니다.. 꼭 알아두세요 피가되고 살이됩니다.(물론 내공 충만하신 분들은 이미 사용하는 방법일 수도 있습니다)

@와 #에 대해 이해를~ 했다면 여기서 동적으로 컬럼을 만드는 해결책에 대한 답을 제시합니다.
즉, 분류가 추가되거나 삭제될 때 유연하게 대처하는 쿼리입니다. 역시나 필자가 실무에서 작성했던 쿼리의 일부를 제시하면 아래와 같습니다.
위의 예제 아래와 같이 다 작성하기 귀잖아서 그냥 작성했던거 가지고 설명합니다 ㅎㅎㅎ

  SET @WeekStr = ''   
  CREATE TABLE #tblOFCastRun( IsEditable varchar(6),ManCode varchar(10),InOut varchar(10),Src Varchar(6),Stream NVarchar(50)
        ,DataStreamType INT,DataStreamSubType INT)
  CREATE  TABLE #tblItemData (CollaborID INT,ProdHierID INT, SWeek varchar(7),DataStreamType INT, DataStreamSubType INT, Qty DECIMAL(10,2))
  CREATE TABLE  #tblWeek (SEQN INT IDENTITY( 1, 1),YYYYWW CHAR(7))

  INSERT INTO #tblWeek
  SELECT 'W'+ YYYYWW
  FROM dbo.fn_TimeLine(@OrgID)    
  WHERE YYYYWW BETWEEN @STARTWEEK AND @ENDWEEK    
  ORDER BY YYYYWW

  --위에서도 봤겠지만 이부분이 원하는 필드명으로 문자열연결하는 것이고
  SELECT @WeekStr =   @WeekStr  + YYYYWW  + ' INT'  + ','
  FROM #tblWeek 
  ORDER BY YYYYWW

  SET @WeekStr = SubString(@WeekStr,1,Len(@WeekStr) -1) --이건 제일마지막 ,없애려고 하는 것이고
 
  --이부분이 핵심입니다. 
  --열결한 필드명을 Alter table로 임시테이블의 컬럼을 와라락 널려버립니다 콤마로 구분한 문자열에 해당하는 컬럼을 생성 해 준다는거~ 
  -- #tblOFCastRun을 프로시져변수인 #으로 선언한 이유는 동적쿼리에서 사용해도 유효하게 하기 위해서라는거~~
  Declare @tblStr varchar(4000)
  Set @tblStr = 'ALTER TABLE #tblOFCastRun ADD ' +  @WeekStr 
  Execute(@tblStr) 

  --컬럼을 만들고 나면 Case문의 제일 오른쪽에 필드 지정하는 부분 안해줘도 되겠죠...? 직접해보세요~
  --여기까지 하고나면 코드값이 추가되어도 문제없고 컬럼명도 코드값으로(또는 원하는 명)으로 마음대로 매핑 할 수가 있습니다.
  -- 바로 아래코드는 tblOFCastRun 의 필드수만큼을 리턴하는 
sps_SURun 이라는 프로시져를 호출하는 예입니다. 참고만하세요~
  INSERT INTO #tblOFCastRun -- 이렇게 
  EXEC sps_SURun @UserLogonName,@SupplyPolicyID,@RuleSetID,'STN',@PlanTo,@SafetyStockType,@SafetyStock, @WeekBase,@WeekType,@WeekInterval
         ,@TotalFrozen,@FrozenStream, @MinOrderQty,@OrderIncQty,'',''

 
2. Column형식의 Data를 Row형식으로 변환
  이 경우는 1번글의 Bulk insert/update시 Biz레이어에서 XML을 레코드별로 만들어 버리면 애플리케이션에서 해결되는 문제이지만... 필요한 경우 UI에서 보이는 그대로 XML로 만들어 넘겨야 하는 경우에 대처하는 방법입니다
보통 UI에서는 2번에서처럼 컬럼단위를 보기좋게 data를 가공하여 보게되는데 그 컬럼단위로 가공되어진 Data를 어떻게 원복시켜서 DB에 Insert/Update시 테이블형식으로 참여시키느냐 하는 분제애 대한 해결책입니다.

역시 실무코드의 일부입니다. 위에서 사용하 코드의 연장선에 있는 코드입니다.
아까전에 #tblWeek 가 동적쿼리에 참여한다고 적었는데 여기에서 Cross join시에 참여하게 됩니다.

  Declare @iCnt  INT, @iTotalWeek INT
  SET @iCnt  = 1
  SELECT @iTotalWeek = Count(*) FROM #tblWeek -- 총주차를 구함

  SET @tblStr = ''
  SET @tblStr = 'INSERT INTO #tblItemData SELECT ' +  Convert(Varchar(10),@CollaborID )+ ' , '+ Convert(Varchar(10),@ProdHierID) + ', B.YYYYWW SWeek, DataStreamType , DataStreamSubType --여기까지는 키값
           ,CASE B.SEQN '
   WHILE @iCnt <= @iTotalWeek
   BEGIN
       SET @tblStr = @tblStr + ' WHEN  ' + Convert(Varchar(10),@iCnt ) 
                                   + ' THEN  ' + (SELECT YYYYWW FROM #tblWeek WHERE SEQN = Convert(Varchar(10),@iCnt) )
    SET @iCnt = @iCnt + 1
   END
  SET @tblStr = @tblStr + ' END AS Qty '
  SET @tblStr = @tblStr + 'FROM (SELECT *  FROM #tblOFCastRun WHERE ManCode IN (''SS'',''RF'',''PI'')  ) A CROSS JOIN  #tblWeek B '
  
  Execute(@tblStr)

간단히 설명하고 마칩니다
Column형식으로(즉 커럼이 분류값이죠)되어있는 테이블과 분류테이블(주차)과 크로스조인을 하여 컬럼값과 동일한 주차값에 해당하는 수량을 Qty로 모아버리면 끝납니다. 조금만 신경써서 테스트 해 봇면 금방이해갑니다.

바잉~~ 


핑백

  • Business into software : 쿼리 시작부터 끝까지~ 2017-03-02 14:52:27 #

    ... ( 행을 컬럼으로 변환하는 Case문의 응용에 해당 ) *** Case문에 대한 더 많은 응용은 여기를 참고! *** 행열 변환에 관련된 더 상세한 내용은 여기를 참고 ! ( TSQL 예이지만 MySql에서도 거의 작동하는 쿼리문들임 ) 5.2.2 회원정보 접속정보 조회. -- CodeMaster에 로그인/아웃 ... more



구글광고