[SQL] 열을 행으로 바꾸는 방법 정리, CONECT BY LEVEL/UNION ALL/UNPIVOT

출처: https://selgii.tistory.com/56

 

계좌번호 (ACCT_NO)	고객아이디 (CUST_NO)	대출종류1 금액 (LOAN_COST1)	대출종류2 금액 (LOAN_COST2)	대출종류3 금액 (LOAN_COST3)	대출종류 4 금액 (LOAN_COST4)	대출종류5 금액 (LOAN_COST5)
12345678	1234	500	0	200	0	100

---

계좌번호 (ACCT_NO)	고객번호 (CUST_NO)	대출종류 (LOAN_DF)	금액 (COST)
12345678	1234	1	500
12345678	1234	3	200
12345678	1234	5	100

위의 테이블 구조에서 아래 테이블 구조로 변경해서 사용할 일

즉 열을 행으로 바꿔야 하는 일이 생겼는데 UNPIVOT을 생각하지 못한 나를 반성하며 써본다..

 

1. CONNECT BY LEVEL  사용하기

처음에 했던 방법은 기존에 있는 소스에서 최대한 변경안 하려고  

CONNECT BY LEVEL 을 사용해서 반복시키면서 열 구조를 행으로 바꾸려고 했다.

📍참고 : CONNECT BY LEVEL은 for문처럼 활용할 수 있는 oracle에서 순번 채번할 때 주로 사용

SELECT
      A.ACCT_NO
     ,A.CUST_NO
     ,B.LOAN_LVL AS LOAN_DF
     ,DECODE(B.LOAN_LVL,1,LOAN_COST1,2,LOAN_COST2,3,LOAN_COST3,4,LOAN_COST4,5,LOAN_COST5) AS COST 
FROM
(
    SELECT A.ACCT_NO
      	 , A.CUST_NO
     	 , B.LOAN_COST1
         , B.LOAN_COST2
         , B.LOAN_COST3
         , B.LOAN_COST4
         , B.LOAN_COST5
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
)A,
(SELECT to_char(LEVEL) LOAN_LVL
   FROM dual
   CONNECT BY LEVEL <= 5
)B

사실 이렇게 해도 되긴되는데 예제로 짠 위에 쿼리는 5개 정도지만 실제로 업무상으로는 18개의 종류가 있고 하루치 데이터만 조회해도 6만 건이 넘어서 쿼리 한번 호출하는데 50초가 넘게 걸려서 충격받고 이건 아니라 생각했다.

CONNECT BY LEVEL은 대량의 테스트 데이터를 만들거나 동일 데이터를 여러 건 만들 수 있다는걸 알았지만

 

이미 대용량인 데이터를 복제하니까 너무 느려서 이럴때 사용하는 건 아니라는 걸 깨닫고 다음 방법으로 넘어갔다.

 

2. UNION ALL 사용하기

이제 다시 생각해보니까 이게 더 멍청한 방법 같은데.. 시간을 줄여야만 해서 내 머리로는 이게 다였다..

📍참고 : UNION ALL은 중복제거를 하지않고 각 쿼리의 모든 결과를 포함한 합집합

SELECT
      A.ACCT_NO
     ,B.CUST_NO
     ,A.LOAN_DF
     ,A.COST
FROM
(
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '1' AS LOAN_DF
          , B.LOAN_COST1 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '2' AS LOAN_DF
          , B.LOAN_COST2 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '3' AS LOAN_DF
          , B.LOAN_COST3 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '4' AS LOAN_DF
          , B.LOAN_COST4 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '5' AS LOAN_DF
          , B.LOAN_COST5 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
  )A

 

동일한 테이블에 18번 접근해서 처리하지만 LEVEL처럼 같은 조회를 18번 실행하는 게 아니니까 당연히 시간이 줄었다.

하지만 줄어도 15초이상이 걸렸고 UNION ALL 18개는 무모했고.. 멍청했고...

당연히 이상하다고 생각했으나 더 좋은 방법이 떠오르지 않았고 눈물 닦아야 해서 하루는 더 걸리겠다고 생각했다.

근데 오늘 다른 분한테 보여줬더니 그렇게 짜면 다른 사람들이 보면 기절한다고 하면서 UNPIVOT으로 바꾸는 게 어떠냐고 했다

 

3. UNPIVOT 사용하기

내 사전에 야근은 없는데.. 야근하면서 알아낸 UNPIVOT

📍참고 : UNPIVOT은 PIVOT이 행을 열로 변경해 준다면 반대로 열을 행으로 변경해 줄 때 사용

SELECT A.ACCT_NO
     , A.CUST_NO
     , LOAN_DF
     , COST
FROM
(
	SELECT A.ACCT_NO
      	  , A.CUST_NO
     	  , B.LOAN_COST1 AS COST1 
          , B.LOAN_COST2 AS COST2
          , B.LOAN_COST3 AS COST3
          , B.LOAN_COST4 AS COST4
          , B.LOAN_COST5 AS COST5
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
  )A
  UNPIVOT (COST FOR LOAN_DF IN (COST1 AS '1', COST2 AS '2', COST3 AS '3',COST4 AS '4',COST5 AS '5'))

이걸로 바꾸고 5초로 줄었다... 5초.... 5초!!!!!!!!!!!!!!!!!

알고 보니 대용량데이터를 처리할 때는 오히려 LEVEL이나 UNION으로 중복데이터를 계속 생성하는 것보다 UNPIVOT을 사용하는 게 더 빨랐다..

이런 함수 사용하면 더 느릴 거라고 생각했는데 생각해 보면 느린데 왜 만들어줬겠어? 더 똑똑한 사람들이 저런 거 만들었을 텐데!!!!!!!

 

4. 결론

👉 CONNECT BY LEVEL

    대량의 테스트 데이터를 만들거나 동일 데이터를 여러 건 만들 때 유용
    주의점은 복제하는 데이터의 량이 많은 경우 성능장애를 발생
    보통 몇천 건 이하로만 사용하는 것을 권장

👉 UNION ALL

  동일 테이블에 여러 번 접근하여 처리하는 방법
  데이터양이 많은 경우에 권장하지 않음

👉 UNPIVOT

 오라클 11g 이상 버전에서 사용 가능
 가장 간단한 방법 중 하나로 속도도 좋은 장점

⭐️  데이터가 적을 때는 굳이 UNPIVOT을 사용할 필요 없이 CONNECT BY LEVEL이나 UNION ALL로 합쳐도 충분할 것 같아서

테이블을 변경하려는 목적에 따라서 올바른 방법을 선택하면 될 것 같다.

 

[참고]

What is the ANSI standard SQL equivalent of UNPIVOT?

1

In T-SQL, PIVOT is redundant because you can always replace it with a combination of CASE WHEN and GROUP BY. I presume that the same is true of UNPIVOT. If I had some T-SQL UNPIVOT code that I wanted to conform to the SQL standard (let's say, the current ANSI), what would I replace the UNPIVOT with? My best guess so far is some usage of UNION ALL?

A good answer would show a quick example of such a conversion.

• sql
• t-sql
• unpivot
• ansi-sql

Share

Improve this question

Follow

edited Sep 12, 2022 at 19:40

 

asked Sep 12, 2022 at 11:31

 

J. Mini

1,96811 gold badge1111 silver badges4545 bronze badges

• 1
  Yes, UNION ALL is typically used. 

  – jarlh

   Sep 12, 2022 at 11:36
•  
  Yes, it can be replaced by UNION ALL, but the specifics depend on the implementation details. For example, SQL Server filters out NULL values from the source and those will not be shown in the result. 

  – Pred

   Sep 12, 2022 at 11:37
•  
  Yes - see unpivot the data here: mssqltips.com/sqlservertip/7233/… (note: they use union instead of union all... that would have the side effect of removing any duplicates from the results (e.g. as would happen if you included distinct... though generally the data used for an unpivot wouldn't be likely to have duplicates anyway, so it's probably not an issue which is used). 

  – JohnLBevan

   Sep 12, 2022 at 11:39

Add a comment

1 Answer

Sorted by:

                                              Highest score (default)                                                                   Trending (recent votes count more)                                                                   Date modified (newest first)                                                                   Date created (oldest first)                              

1

 

For an UNPIVOT such as this:

SELECT u.*
FROM YourTable AS t
UNPIVOT (
    ColumnValue FOR ColumnName IN (
        Unpivot1, Unpivot2, Unpivot3
    )
) AS u;

You can do the same thing with a Lateral Join and a VALUES constructor

SELECT u.*
FROM YourTable AS t
CROSS JOIN LATERAL (VALUES
    ('UnPivot1', t.Unpivot1),
    ('UnPivot2', t.Unpivot2),
    ('UnPivot3', t.Unpivot3)
) AS u(ColumnName, ColumnValue);

In SQL Server this can be done using CROSS APPLY

SELECT u.*
FROM YourTable AS t
CROSS APPLY (VALUES
    ('UnPivot1', t.Unpivot1),
    ('UnPivot2', t.Unpivot2),
    ('UnPivot3', t.Unpivot3)
) AS u(ColumnName, ColumnValue);

I normally recommend this method even in SQL Server, as it's much more flexible. For example, you can unpivot multiple columns at a time.

---

Exactly which product you are using that supports ANSI-SQL perfectly is a different question, I'm not aware of any that do. you are best off just using the syntax that your product provides.