[MSSQL] 가변컬럼 SQL

데이터를 만지다 보면

집계를 하기위한 여러가지 삽질을 하게 된다.

나같은 경우에는 

액셀로 내보내서 작업을 하는 경우가 많은데, 

하다보니 그것도 귀찮음.

액셀에 피봇테이블 이던가.. (아님말구) 음.. 암튼 종을 횡으로 바꿀 필요가 있다.

그런데 나같은 경우에 신용카드 승인 결과를 리포팅 하는데, 승인 오류 코드가 엄청나게 많은게 문제..

그걸 컬럼으로 나열하자니.. 엄청남.

그리고, 그중에 값이 없는 쓰레기 값들이 넘 많더라

그래서

가상테이블을 활용해서

가변 컬럼 쿼리를 만들었 봤다.

DBA가 아닌관계로 쿼리 자체가.. 그닥 깔끔하지 못해.

그리고 실무에 사용되는 DB 테이블 그대로라

만약 여기 들어와서 보시는 분들은 먼 소린지 모를지도.... (그런데 들어오는 사람이 거의 없어 별 걱정 않함)

/* 요건 그냥 조회하기위한 변수 설정 */ 

declare @idx  varchar(8)

declare @idx_TO  varchar(8)

set @idx = '20160101'

set @idx_TO = '20170131'

/*실제 데이터 부분의 임시테이블을 만들어 줌.  */ 

CREATE TABLE #AA (IDX VARCHAR(8), ERR_TEXT  VARCHAR(50), MAKE_COM VARCHAR(30), AMOUNT  NUMERIC(18, 0), RETURN_CODE VARCHAR(4))

INSERT INTO #AA

select k.idx, isnull((select nm1 from tb_comcd where cd1='9000' and cd2=kcc_snd_code),'기타') as err_text,

B.MAKE_COM, convert(numeric,k.tran_amount ) amount, kcc_snd_code

from KICC_BATCH_DATA k left outer join TB_CARD_BIN b on LEFT(K.card_no,6) = B.BIN where k.idx BETWEEN @idx AND @IDX_TO;

/* 위의 #AA 테이블과 동일한 조건에서 컬럼으로 표시할 부분만 쿼리&묶음(group by) */ 

CREATE TABLE #BB (ERR_TEXT  VARCHAR(50))

INSERT INTO #BB

select * from

(select isnull((select nm1 from tb_comcd where cd1='9000' and cd2=kcc_snd_code),'기타') as err_text

from KICC_BATCH_DATA where idx BETWEEN @idx AND @idx_TO ) x1 group by x1.err_text

 

위에서 만들어진 가상테이블 #AA, #BB의 내용을 보면 아래와 같이 되어있다.

#AA TABLE	#BB TABLE

생성된 가상테이블 #AA, #BB를 활용해서 

새로운 SQL문을 만들어야 한다.

고정컬럼 부분+유동컬럼 부분

SQL 문이 결과값으로 나오도록  작성.

유동컬럼 부분이 잘 나오도록 조합하는게 관건.

/* 새로 생성할 sql을 변수로.. 사이즈 모르니 varchar(max)*/

Declare @Sql varchar(max)

/* ① SQL의 시작 부분, 고정컬럼 */

set @Sql = 'select #AA.IDX, #AA.make_com as "카드사", SUM(#AA.AMOUNT) AS "요청금액", COUNT(#AA.IDX) AS "요청건수", SUM(CASE #AA.RETURN_CODE WHEN ''0000'' THEN convert(numeric,#AA.AMOUNT ) ELSE 0 END) SI_AMOUNT'

/* ② SQL의 중간 부분, 유동컬림 부분이지만 유동중에 최초 나와야 하는 값(정상승인)이 있어서 따로 하나를 앞으로 뺐다.*/

select @sql=@Sql +', SUM(CASE WHEN #BB.ERR_TEXT = '''+ #BB.ERR_TEXT +''' THEN 1 ELSE 0 END) AS  ' +'"'+#BB.ERR_TEXT+'"'

        FROM #BB WHERE #BB.ERR_TEXT = '정상승인'

/* ③ 본격적인 유동컬럼 부분, 결과값 만큼(#BB만큼의 ROW만큼) 컬럼이 생성된다.*/

select @sql=@Sql +', SUM(CASE WHEN #BB.ERR_TEXT = '''+ #BB.ERR_TEXT +''' THEN 1 ELSE 0 END) AS  ' +'"'+#BB.ERR_TEXT+'"'

        FROM #BB WHERE #BB.ERR_TEXT <> '정상승인'   

/* ④ 테이블 조인 및 조건절*/

SELECT @Sql = @Sql + 'FROM #AA JOIN #BB ON #AA.err_text = #BB.err_text GROUP BY #AA.IDX, #AA.make_com order by #AA.IDX, #AA.make_com'

/* 생성된 SQL 조회 */

SELECT @Sql

SELECT @Sql 해서 만들어진 SQL을 확인하면 다음과 같다.

/* ① SQL */

select

  #AA.IDX,

  #AA.make_com as "카드사",

  SUM(#AA.AMOUNT) AS "요청금액",

  COUNT(#AA.IDX) AS "요청건수",

  SUM(CASE #AA.RETURN_CODE WHEN '0000' THEN convert(numeric,#AA.AMOUNT ) ELSE 0 END) SI_AMOUNT,

/* ② SQL */

  SUM(CASE WHEN #BB.ERR_TEXT = '정상승인' THEN 1 ELSE 0 END) AS  "정상승인",

/* ③ SQL */

  SUM(CASE WHEN #BB.ERR_TEXT = '카드번호 입력 오류' THEN 1 ELSE 0 END) AS  "카드번호 입력 오류",

                     ---- 중략 ---

  SUM(CASE WHEN #BB.ERR_TEXT = '기타' THEN 1 ELSE 0 END) AS  "기타",

  SUM(CASE WHEN #BB.ERR_TEXT = '유효기간 경과 카드' THEN 1 ELSE 0 END) AS  "유효기간 경과 카드",

  SUM(CASE WHEN #BB.ERR_TEXT = '거래정지 카드' THEN 1 ELSE 0 END) AS  "거래정지 카드",

  SUM(CASE WHEN #BB.ERR_TEXT = '탈회/해지 카드' THEN 1 ELSE 0 END) AS  "탈회/해지 카드",

  SUM(CASE WHEN #BB.ERR_TEXT = '사고등록 계좌' THEN 1 ELSE 0 END) AS  "사고등록 계좌",

  SUM(CASE WHEN #BB.ERR_TEXT = '잔액 부족' THEN 1 ELSE 0 END) AS  "잔액 부족"

/* ④ SQL */

  FROM #AA JOIN #BB ON #AA.err_text = #BB.err_text

  GROUP BY #AA.IDX, #AA.make_com

  order by #AA.IDX, #AA.make_com

처음 가상테이블 #AA , #BB 생성할때의 조건에 따라서 #BB의 값이 달라지고

따라서 @SQL 의 ③부분의 컬럼 갯수가 달라지게 됩니다.

만들어진 SQL을 복사 붙여넣기해서 실행해도 되지만.

요렇게 실행하는게 더 편함

EXEC(@sql)

결과는 이런식으로 나오게 된다. 

끝.