데이터베이스 질문과 답변
| A1 | B1 | ||
| C2 | |||
| D3 | |||
| B4 | D4 |
위와같은 데이터가있을때 아래와같이 각 컬럼별로 데이터가있는것들만 얻고싶은데 좋은 방법이 떠오르지 않습니다.
| A1 | B1 | C2 | D3 |
| B4 | D4 |
어떻게 쿼리하면 좋을까요.ㅠㅠ
with tmp as (
select 'A1' a,'B1' b ,null c ,null d from dual union all
select null,null,'C2' ,null from dual union all
select null,null, null,'D3' from dual union all
select null,'B4',null ,'D4' from dual
)
select * from tmp
by 마농
[2019.11.06 15:34:45]
WITH tmp AS
(
SELECT 'A1' a, 'B1' b, null c, null d FROM dual
UNION ALL SELECT null, null, 'C2', null FROM dual
UNION ALL SELECT null, null, null, 'D3' FROM dual
UNION ALL SELECT null, 'B4', null, 'D4' FROM dual
)
SELECT *
FROM (SELECT gb, v
, ROW_NUMBER() OVER(PARTITION BY gb ORDER BY v) rn
FROM tmp
UNPIVOT (v FOR gb IN (a, b, c, d))
)
PIVOT (MIN(v) FOR gb IN ('A' a, 'B' b, 'C' c, 'D' d))
ORDER BY rn
;
by 우리집아찌
[2019.11.07 08:04:38]
원본 대이터가 저렇게 되어있나요?
) 버튼을 클릭하여 작성 하시면 됩니다.