SELECT
BANK_CD = -- 은행코드
CASE WHEN A.BANK_CD = '10000004' THEN '04'
WHEN A.BANK_CD = '10000081' THEN '81'
WHEN A.BANK_CD = '10000088' THEN '88'
END
,A.ACCT_NO -- 계좌번호
,A.ACCT_TXDAY --거래일자
,SUM(A.TX_AMT) AS DR --거래금액합계
,NULL AS CR
,MAX(A.INS_DATETIME) AS INS_DATETIME --입력일자
,A.JEOKYO
,MAX(A.ACCT_TXDAY_SEQ) AS ACCT_TXDAY_SEQ --거래 순번
,A.INOUT_GUBUN --입/출 구분(1 출금, 2입금)
,A.ACCT_TXTIME
FROM ISS_ACCT_HIS A
WHERE
ACCT_NO = '50908475000604' AND
A.ACCT_TXDAY = '20051215'
AND A.INOUT_GUBUN = '1'
GROUP BY A.BANK_CD
,A.ACCT_NO
,A.ACCT_TXDAY
,A.JEOKYO
,A.INOUT_GUBUN
,A.ACCT_TXTIME
--상단은 계좌별 출금액의 합을 구하게 됩니당.
UNION ALL
SELECT
BANK_CD =
CASE WHEN A.BANK_CD = '10000004' THEN '04'
WHEN A.BANK_CD = '10000081' THEN '81'
WHEN A.BANK_CD = '10000088' THEN '88'
END
,A.ACCT_NO
,A.ACCT_TXDAY
,NULL AS DR
,SUM(A.TX_AMT) AS CR
,MAX(A.INS_DATETIME) AS INS_DATETIME
,A.JEOKYO
,MAX(A.ACCT_TXDAY_SEQ) AS ACCT_TXDAY_SEQ
,A.INOUT_GUBUN
,A.ACCT_TXTIME
FROM ISS_ACCT_HIS A
WHERE
ACCT_NO = '50908475000604' AND
A.ACCT_TXDAY = '20051215'
AND A.INOUT_GUBUN = '2'
GROUP BY A.BANK_CD
,A.ACCT_NO
,A.ACCT_TXDAY
,A.JEOKYO
,A.INOUT_GUBUN
,A.ACCT_TXTIME
-- 상단은 입금액의 합을 구하게 됩니당.
이 를 유니온 하면 결과값 처럼 DR값과 CR값이 분리되어서 2열이 나오는데
이를 한 열로 합칠수 없을까요 ???? ㅠㅠ
Comment 1
-
백서리
2014.12.29 12:58
자문 자답...
SELECT
A.BANK_CD
,A.ACCT_NO
,A.ACCT_TXDAY
,A.DR
,A.CR
,B.INS_DATETIME
,B.JEOKYO
,B.ACCT_TXDAY_SEQ
,B.INOUT_GUBUN
,B.ACCT_TXTIME
FROM (
SELECT
BANK_CD =
CASE WHEN A.BANK_CD = '10000004' THEN '04'
WHEN A.BANK_CD = '10000081' THEN '81'
WHEN A.BANK_CD = '10000088' THEN '88'
END
,ACCT_NO
,ACCT_TXDAY
,SUM(CASE INOUT_GUBUN WHEN '1' THEN TX_AMT ELSE 0 END) AS DR
,SUM(CASE INOUT_GUBUN WHEN '2' THEN TX_AMT ELSE 0 END) AS CR
FROM ISS_ACCT_HIS A
WHERE
ACCT_TXDAY = '20051215'
GROUP BY BANK_CD, ACCT_NO, ACCT_TXDAY ) A
,
(
SELECT
BANK_CD =
CASE WHEN A.BANK_CD = '10000004' THEN '04'
WHEN A.BANK_CD = '10000081' THEN '81'
WHEN A.BANK_CD = '10000088' THEN '88'
END
,ACCT_NO
,ACCT_TXDAY
,MAX(INS_DATETIME) AS INS_DATETIME
,JEOKYO
,MAX(ACCT_TXDAY_SEQ) AS ACCT_TXDAY_SEQ
,INOUT_GUBUN
,ACCT_TXTIME
FROM ISS_ACCT_HIS A
WHERE
ACCT_TXDAY = '20051215' AND
INOUT_GUBUN = '1'
GROUP BY BANK_CD, ACCT_NO, ACCT_TXDAY,
JEOKYO, INOUT_GUBUN, ACCT_TXTIME
UNION ALL
SELECT
BANK_CD =
CASE WHEN BANK_CD = '10000004' THEN '04'
WHEN BANK_CD = '10000081' THEN '81'
WHEN BANK_CD = '10000088' THEN '88'
END
,ACCT_NO
,ACCT_TXDAY
,MAX(INS_DATETIME) AS INS_DATETIME
,JEOKYO
,MAX(ACCT_TXDAY_SEQ) AS ACCT_TXDAY_SEQ
,INOUT_GUBUN
,ACCT_TXTIME
FROM ISS_ACCT_HIS
WHERE
ACCT_TXDAY = '20051215' AND
INOUT_GUBUN = '2'
GROUP BY BANK_CD, ACCT_NO, ACCT_TXDAY,
JEOKYO, INOUT_GUBUN, ACCT_TXTIME ) B
WHERE A.BANK_CD = B.BANK_CD
AND A.ACCT_NO = B.ACCT_NO
AND A.ACCT_TXDAY = B.ACCT_TXDAY
ORDER BY ACCT_NO
대충 이렇게 해결완료 ;;