데이터베이스 질문과 답변
한컬럼에 2개의 데이타를 보이게 하고 싶은데요.
0
4
2,159
by 길은정 [2012.08.16 16:54:02]
날짜 합계
2012-08-27 18
ㅁㅁ (a:3, b:5, c:10)
2012-08-28 5
ㅁ ㅁ (a:4, b:1, c:0)
이런식으로 합계는 18 인데 그 합계의 각각을 18 숫자 바로 밑에 (a:3, b:5, c:10)
표현을 하고 싶은데요.
쿼리로 어떻게 가능할까요??...아 머리아포요. -ㅠ-
2012-08-27 18
ㅁㅁ (a:3, b:5, c:10)
2012-08-28 5
ㅁ ㅁ (a:4, b:1, c:0)
이런식으로 합계는 18 인데 그 합계의 각각을 18 숫자 바로 밑에 (a:3, b:5, c:10)
표현을 하고 싶은데요.
쿼리로 어떻게 가능할까요??...아 머리아포요. -ㅠ-
by 손님
[2012.08.16 17:10:34]
WITH T AS (
SELECT SYSDATE C, 'a' A, 3 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'b' A, 5 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'c' A, 10 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'a' A, 4 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'b' A, 1 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'c' A, 0 B FROM DUAL
)
SELECT C, DECODE(LV,2,WM_CONCAT(A||':'||B),SUM(B)) B
FROM T
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <=2)
GROUP BY C,LV
ORDER BY C
by 손님
[2012.08.16 17:37:08]
WITH T AS (
SELECT SYSDATE C, 'a' A, 3 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'b' A, 5 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'c' A, 10 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'a' A, 4 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'b' A, 1 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'c' A, 0 B FROM DUAL
)
SELECT DECODE(LV,1,C,NULL) C, DECODE(LV,2,A,B) AS A
FROM (
SELECT C, MAX(SUBSTR(SYS_CONNECT_BY_PATH(A||':'||B,','),2)) AS A, SUM(B) AS B
FROM (
SELECT T.*, DECODE(A,'a',1,'b',2,'c',3) AS D
FROM T
) T
START WITH D = 1
CONNECT BY C = PRIOR C AND D = PRIOR D+1
GROUP BY C
),
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 2)
;
by 솔밭
[2012.08.16 18:24:15]
WITH T AS (
SELECT SYSDATE C, 'a' A, 3 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'b' A, 5 B FROM DUAL UNION ALL
SELECT SYSDATE C, 'c' A, 10 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'a' A, 4 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'b' A, 1 B FROM DUAL UNION ALL
SELECT SYSDATE+1 C, 'c' A, 0 B FROM DUAL
)
SELECT C, DECODE(LV,2,ListAgg(A||':'||B, ',') WITHIN GROUP(ORDER BY A)) B
FROM T
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <=2)
GROUP BY C,LV
ORDER BY C
by 깨비
[2012.08.17 10:07:27]
감쏴요~ ^^/
) 버튼을 클릭하여 작성 하시면 됩니다.