데이터베이스 질문과 답변
unpivot grouping 문의드립니다..
0
2
767
by kimmmmmmm [2019.07.03 17:28:23]
A B C D
---------------------------
1 2-1 3-1 4-1
1 2-1 3-1 4-2
1 2-1 3-1 4-3
1 2-2 3-2 4-4
1 2-2 3-3 4-5
1
2-1
3-1
4-1
3-1
4-2
3-1
4-3
2-2
3-2
4-4
3-3
4-5
안녕하세요, 해당 데이터를 위와같은 결과로 뽑고싶은데..
A, B 컬럼은 unpivot하면서 group by로 묶고, C, D 컬럼은 goup by 하지 않고 내리려고 하고있습니다.
해당 쿼리가 가능하긴 한가요..?
계층쿼리, 언피벗 뭘해도 적용을 할 수 없어 문의드립니다 ㅠㅠ
by 마농
[2019.07.03 17:52:46]
WITH t AS
(
SELECT '1' a, '2-1' b, '3-1' c, '4-1' d FROM dual
UNION ALL SELECT '1', '2-1', '3-1', '4-2' FROM dual
UNION ALL SELECT '1', '2-1', '3-1', '4-3' FROM dual
UNION ALL SELECT '1', '2-2', '3-2', '4-4' FROM dual
UNION ALL SELECT '1', '2-2', '3-3', '4-5' FROM dual
)
SELECT e
FROM (SELECT COALESCE(c, b, a) c
, d
FROM t
GROUP BY a, ROLLUP(b, (c, d))
ORDER BY a, b NULLS FIRST, t.c NULLS FIRST, d
)
UNPIVOT (e FOR gb IN (c, d))
;
by jkson
[2019.07.03 17:53:47]
sort 기준은 한눈에 잘 안 보이네요. 퇴근시간이라 후다닥.
아하 sort 기준이 요고네요~
with t as
(
select '1' a, '2-1' b, '3-1' c, '4-1' d from dual union all
select '1' a, '2-1' b, '3-1' c, '4-2' d from dual union all
select '1' a, '2-1' b, '3-1' c, '4-3' d from dual union all
select '1' a, '2-2' b, '3-2' c, '4-4' d from dual union all
select '1' a, '2-2' b, '3-3' c, '4-5' d from dual
)
select max(val) val
from
(
select gb, val, row_number() over(partition by gb order by val) rn
from t
unpivot (val for gb in (a, b, c, d))
)
group by gb, decode(gb,'A',val,'B',val,rn)
order by min(rn), gb, val -- 요거 추가
) 버튼을 클릭하여 작성 하시면 됩니다.