MS-SQL 계층구조로 데이터 가져오기 질문입니다. 1 2 3,732

by raksasa [DB 기타] MSSQL MS-SQL 계층구조 [2015.09.04 09:17:37]


2개의 테이블이 있는데 하나는 코드 리스트 테이블로 이러한 모양이고

 

코드

상위코드

LEVEL

EDIT_YN

A

 

1

N

A1

A

2

N

A1-1

A1

3

Y

A1-2

A1

3

Y

A2

A

2

Y

B

 

1

N

B1

B

2

Y

B2

B

2

Y

 

다른 테이블은 이렇게 데이터가 있는 테이블입니다.

 

코드

구분

A1-1

2

A1-1

3

A1-1

2

A1-2

4

A1-2

4

A1-2

1

A2

2

B1

5

B1

1

B2

3

B2

2

 

(코드 리스트의 EDIT_YN의 값이 Y인 것의 데이터만 존재)

 

두 테이블을 이용하여 아래 모양의 결과를 MSSQL(2010)로 가져오고싶습니다.

 

코드

A

18

8

7

3

 A1

16

6

7

3

     A1-1

7

2

3

2

     A1-2

9

4

4

1

 A2

2

2

0

0

B

11

5

3

3

 B1

6

5

0

1

 B2

5

0

3

2

 

  3레벨의 합으로 2레벨 합을 구하고 그 2레벨들의 합으로 1레벨의 값을 구하려는데

MSSQL로는 어떻게 해야할지 알수있을까요?

by 마농 [2015.09.04 11:34:46]
WITH code AS
( -- 코드 테이블 --
SELECT 'A' cd, null pcd, 1 lv, 'N' yn
UNION ALL SELECT 'A1'  , 'A' , 2, 'N'
UNION ALL SELECT 'A1-1', 'A1', 3, 'Y'
UNION ALL SELECT 'A1-2', 'A1', 3, 'Y'
UNION ALL SELECT 'A2'  , 'A' , 2, 'Y'
UNION ALL SELECT 'B'   , null, 1, 'N'
UNION ALL SELECT 'B1'  , 'B' , 2, 'Y'
UNION ALL SELECT 'B2'  , 'B' , 2, 'Y'
)
, data AS
( -- 데이터 테이블 --
SELECT 'A1-1' cd, '가' gb, 2 v
UNION ALL SELECT 'A1-1', '나', 3
UNION ALL SELECT 'A1-1', '다', 2
UNION ALL SELECT 'A1-2', '가', 4
UNION ALL SELECT 'A1-2', '나', 4
UNION ALL SELECT 'A1-2', '다', 1
UNION ALL SELECT 'A2'  , '가', 2
UNION ALL SELECT 'B1'  , '가', 5
UNION ALL SELECT 'B1'  , '다', 1
UNION ALL SELECT 'B2'  , '나', 3
UNION ALL SELECT 'B2'  , '다', 2
)
, tmp0 AS
( -- 코드테이블 계층구조 생성 --
SELECT c.cd, c.pcd, c.lv
     , CAST(c.cd AS VARCHAR(100)) p
     , REPLICATE(' ', c.lv-1) + c.cd AS cds
  FROM code c
 WHERE pcd IS NULL
 UNION ALL
SELECT c.cd, c.pcd, c.lv
     , CAST(p.p + '/' + c.cd AS VARCHAR(100)) p
     , REPLICATE(' ', c.lv-1) + c.cd AS cds
  FROM tmp0 p
 INNER JOIN code c
    ON p.cd = c.pcd
)
, tmp1 AS
( -- 코드별 집계 --
SELECT c.cd, c.pcd, c.lv
     , SUM(d.v) 합
     , ISNULL(SUM(CASE d.gb WHEN '가' THEN v END), 0) 가
     , ISNULL(SUM(CASE d.gb WHEN '나' THEN v END), 0) 나
     , ISNULL(SUM(CASE d.gb WHEN '다' THEN v END), 0) 다
  FROM code c
  LEFT OUTER JOIN data d
    ON c.cd = d.cd
 GROUP BY c.cd, c.pcd, c.lv
)
, tmp2 AS
( -- 각 코드별 하위내역 계층전개 --
SELECT c.cd r_cd
     , c.cd, c.pcd, c.lv
     , c.합, c.가, c.나, c.다
  FROM tmp1 c
 UNION ALL
SELECT p.r_cd
     , c.cd, c.pcd, c.lv
     , c.합, c.가, c.나, c.다
  FROM tmp2 p
 INNER JOIN tmp1 c
    ON p.cd = c.pcd
)
SELECT c.cds
     , 합
     , 가
     , 나
     , 다
  FROM tmp0 c
 INNER JOIN
       ( -- 코드별 하위내역 집계 --
        SELECT r_cd
             , SUM(합) 합
             , SUM(가) 가
             , SUM(나) 나
             , SUM(다) 다
          FROM tmp2
         GROUP BY r_cd
        ) d
    ON c.cd = d.r_cd
 ORDER BY p
;

 


by raksasa [2015.09.04 12:53:14]

감사합니다 ㅠ.ㅠ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입