데이터 그룹화2(LISTAGG, PIVOT, UNPIVOT)

1. LSITAGG 함수

- 가로로 나열할 열을 지정하고 필요하다면 각 데이터 사이에 넣을 구분자를 지정가능

- 오라클 11g버전부터 사용가능

예시)

결과)

DEPARTMENT_ID	NAMES
10	Jennifer
20	Michael, Pat
30	Den, Alexander, Shelli, Sigal, Guy, Karen
40	Susan
50	Adam, Matthew, Payam, Shanta, Kevin, Nandita, Alexis, Sarah, Britney, Kelly, Jennifer, Renske, Trenna, Julia, Jason, Laura, Julia, Samuel, Stephen, Winston, Alana, Curtis, Jean, Anthony, Kevin, Michael, Timothy, Girard, Mozhe, Vance, Irene, John, Donald, Douglas, Randall, James, Joshua, Martha, Peter, Randall, James, Ki, Hazel, Steven, TJ
60	Alexander, Bruce, David, Valli, Diana
70	Hermann
80	John, Karen, Alberto, Lisa, Ellen, Gerald, Clara, Eleni, Harrison, Janette, Peter, Tayler, Danielle, David, Patrick, Allan, Peter, Alyssa, Jonathon, Jack, Christopher, Lindsey, Louise, Nanette, William, Elizabeth, Mattea, Oliver, Sarath, David, Sundar, Amit, Charles, Sundita
90	Steven, Lex, Neena
100	Nancy, Daniel, John, Jose Manuel, Ismael, Luis
110	Shelley, William
-	Kimberely

2. PIVOT

- 오라클 11g버전부터 사용가능

결과)

JOB_ID	10	20	30	40	50
AC_ACCOUNT	-	-	-	-	-
AC_MGR	-	-	-	-	-
AD_ASST	4400	-	-	-	-
AD_PRES	-	-	-	-	-
AD_VP	-	-	-	-	-
FI_ACCOUNT	-	-	-	-	-
FI_MGR	-	-	-	-	-
HR_REP	-	-	-	6500	-
IT_PROG	-	-	-	-	-
MK_MAN	-	13000	-	-	-
MK_REP	-	6000	-	-	-
PR_REP	-	-	-	-	-
PU_CLERK	-	-	3100	-	-
PU_MAN	-	-	11000	-	-
SA_MAN	-	-	-	-	-
SA_REP	-	-	-	-	-
SH_CLERK	-	-	-	-	4200
ST_CLERK	-	-	-	-	3600
ST_MAN	-	-	-	-	8200

결과)

DEPARTMENT_ID	AD_ASST	HR_REF	MK_MAN	MK_REF	PU_CLERK	PU_MAN	SH_CLERK	ST_CLERK	ST_MAN
10	4400	-	-	-	-	-	-	-	-
20	-	-	13000	6000	-	-	-	-	-
30	-	-	-	-	3100	11000	-	-	-
40	-	6500	-	-	-	-	-	-	-
50	-	-	-	-	-	-	4200	3600	8200
60	-	-	-	-	-	-	-	-	-
70	-	-	-	-	-	-	-	-	-
80	-	-	-	-	-	-	-	-	-
90	-	-	-	-	-	-	-	-	-
100	-	-	-	-	-	-	-	-	-
110	-	-	-	-	-	-	-	-	-
-	-	-	-	-	-	-	-	-	-

3. UNPIVOT

- PIVOT와 반대기능

결과)

DEPARTMENT_ID	JOB_ID	SALARY
10	AD_ASST	4400
20	MK_MAN	13000
20	MK_REP	6000
30	PU_CLERK	3100
30	PU_MAN	11000
40	HR_REP	6500
50	SH_CLERK	4200
50	ST_CLERK	3600
50	ST_MAN	8200

PIVOT와 UNPIVOT함수관련 오라클 공식 문서 : https://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html