피벗 함수(11g 이상) 및 피벗 기능(10g 이하)

세로로 나오는 값을 가로로 정리해주는 SQL

SELECT
    COLUMN_NAME,
    DATA_LENGTH,
    MIN(CASE WHEN NO=1 THEN TABLE_NAME END)||
    MIN(CASE WHEN NO=2 THEN ','||TABLE_NAME END)||
    MIN(CASE WHEN NO=3 THEN ','||TABLE_NAME END) AS TABLES
FROM (
       SELECT
             COLUMN_NAME,
             DATA_LENGTH,
             TABLE_NAME,
             ROW_NUMBER() OVER( PARTITION BY COLUMN_NAME, DATA_LENGTH ORDER BY TABLE_NAME) AS NO
        FROM ALL_TAB_COLUMNS
       WHERE OWNER = 'BASE'
     )
GROUP BY COLUMN_NAME, DATA_LENGTH
ORDER BY COLUMN_NAME, DATA_LENGTH
;

결과

COLUMN_NAME                    DATA_LENGTH TABLES
------------------------------ ----------- --------------------------------------------------------------------------------------------
ACPT_DATE                                8 RECP_ACCT
ACPT_NO                                  5 RECP_ACCT
ALPHA2                                   2 CM_ISO_NATIONS,TMP_ENATIONS
ALPHA3                                   3 CM_ISO_NATIONS

                            

※ 11g 이후에 LISTAGG 함수가 생겨서 바로 사용 가능

SELECT
      COLUMN_NAME, DATA_LENGTH,
      LISTAGG(TABLE_NAME,', ') WITHIN GROUP( ORDER BY TABLE_NAME ) AS TABLES
 FROM ALL_TAB_COLUMNS
WHERE OWNER = 'BASE'
GROUP BY COLUMN_NAME, DATA_LENGTH
ORDER BY COLUMN_NAME, DATA_LENGTH
;

결과

COLUMN_NAME                    DATA_LENGTH TABLES
------------------------------ ----------- --------------------------------------------------------------------------------------------
ACPT_DATE                                8 RECP_ACCT
ACPT_NO                                  5 RECP_ACCT
ALPHA2                                   2 CM_ISO_NATIONS, TMP_ENATIONS
ALPHA3                                   3 CM_ISO_NATIONS