초보 입니다ㅜ
질문드립니다~!
SELECT A.NM
, A.TYP
, (SELECT COUNT(TYP) FROM T_M WHERE TYP = A.TYP AND CT <> '') AS RYES
, (SELECT COUNT(TYP) FROM T_M WHERE TYP = A.TYP AND CT = '') AS RNO
, (SELECT COUNT(TYP) FROM T_M WHERE TYP = A.TYP) AS RTOTAL
FROM T_MT AS A
WHERE A.MC='001'
이런쿼리를 실행하면
| NM | TYP | RYES | RNO | RTOTAL |
| 일반 1 | 1 | 4 | 3 | 7 |
| 일반 2 | 2 | 1 | 7 | 8 |
| 일반 3 | 3 | 8 | 2 | 10 |
| 일반 4 | 5 | 0 | 0 | 0 |
| 일반 5 | 6 | 0 | 0 | 0 |
| 일반 6 | 7 | 0 | 0 | 0 |
| 일반 7 | 8 | 0 | 0 | 0 |
| 일반 8 | 9 | 0 | 0 | 0 |
| 일반 9 | 10 | 0 | 0 | 0 |
이런결과가 나옵니다
이럴때
| 일반 1 | 일반 2 | 일반 3 | 일반 4 | 일반 5 | 일반 6 | 일반 7 | 일반 8 | 일반 9 | |
| RYES | 4 | 1 | 8 | 0 | 0 | 0 | 0 | 0 | 0 |
| RNO | 3 | 7 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| RTOTAL | 7 | 8 | 10 | 0 | 0 | 0 | 0 | 0 | 0 |
이렇게 세로 결과물을 가로로 출력 하려면 어떻게 해야 할까요...?
Comment 1
-
자리비움
2017.08.21 11:59
WITH T1 AS(SELECT MC = '001', NM = '일반1', TYP = 1 UNION ALLSELECT MC = '001', NM = '일반2', TYP = 2 UNION ALLSELECT MC = '001', NM = '일반3', TYP = 3),T2 AS(SELECT TYP = 1, CT = 'A' UNION ALLSELECT TYP = 1, CT = 'A' UNION ALLSELECT TYP = 1, CT = 'A' UNION ALLSELECT TYP = 1, CT = 'A' UNION ALLSELECT TYP = 1, CT = '' UNION ALLSELECT TYP = 1, CT = '' UNION ALLSELECT TYP = 1, CT = '' UNION ALLSELECT TYP = 2, CT = 'A' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 2, CT = '' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = 'A' UNION ALLSELECT TYP = 3, CT = '' UNION ALLSELECT TYP = 3, CT = '')SELECT A.[TYPE], [일반1] = MAX(A.일반1), [일반2] = MAX(A.일반2), [일반3] = MAX(A.일반3), [일반4] = MAX(A.일반4)FROM (SELECT A.SEQ, A.[TYPE], [일반1] = (CASE WHEN A.[TYPE] = 'RYES' AND B.NM = '일반1' THEN RYESWHEN A.[TYPE] = 'RNO' AND B.NM = '일반1' THEN RNOWHEN A.[TYPE] = 'RTOTAL' AND B.NM = '일반1' THEN RTOTALELSE 0 END), [일반2] = (CASE WHEN A.[TYPE] = 'RYES' AND B.NM = '일반2' THEN RYESWHEN A.[TYPE] = 'RNO' AND B.NM = '일반2' THEN RNOWHEN A.[TYPE] = 'RTOTAL' AND B.NM = '일반2' THEN RTOTALELSE 0 END), [일반3] = (CASE WHEN A.[TYPE] = 'RYES' AND B.NM = '일반3' THEN RYESWHEN A.[TYPE] = 'RNO' AND B.NM = '일반3' THEN RNOWHEN A.[TYPE] = 'RTOTAL' AND B.NM = '일반3' THEN RTOTALELSE 0 END), [일반4] = (CASE WHEN A.[TYPE] = 'RYES' AND B.NM = '일반4' THEN RYESWHEN A.[TYPE] = 'RNO' AND B.NM = '일반4' THEN RNOWHEN A.[TYPE] = 'RTOTAL' AND B.NM = '일반4' THEN RTOTALELSE 0 END)FROM (SELECT [SEQ] = 1, [TYPE] = 'RYES' UNION ALLSELECT [SEQ] = 2, [TYPE] = 'RNO' UNION ALLSELECT [SEQ] = 3, [TYPE] = 'RTOTAL') AS ACROSS JOIN (SELECT A.NM, A.TYP, RYES = SUM(CASE WHEN B.CT <> '' THEN 1 ELSE 0 END), RNO = SUM(CASE WHEN B.CT = '' THEN 1 ELSE 0 END), RTOTAL = SUM(1)FROM T1 AS AJOIN T2 AS B ON B.TYP = A.TYPWHERE A.MC = '001'GROUP BY A.NM, A.TYP) AS BGROUP BY A.SEQ, A.[TYPE], B.NM, B.RYES, B.RNO, B.RTOTAL) AS AGROUP BY A.SEQ, A.[TYPE]ORDER BY A.SEQ ASC
