데이터베이스 질문과 답변
| A | B | C | D | E |
| DATA1 | DATA2 | DATA1 | DATA4 | DATA1 |
| DATA2 | DATA4 | DATA2 | DATA3 | DATA3 |
| DATA3 | DATA5 | DATA3 | DATA2 | DATA5 |
| DATA5 | DATA1 | DATA4 | DATA1 | DATA2 |
↓
| DATA1 | DATA2 | DATA3 | DATA4 | DATA5 | |
| A | 1 | 1 | 1 | 0 | 1 |
| B | 1 | 1 | 0 | 1 | 1 |
| C | 1 | 1 | 1 | 1 | 0 |
| D | 1 | 1 | 1 | 1 | 0 |
| E | 1 | 1 | 1 | 0 | 1 |
이런식으로 가로열을 세로열로 변경할 수 있을까요..ㅜㅜ 찾아보는데 너무 헷갈리네요
by jkson
[2017.02.20 13:33:20]
with t(a,b,c,d,e)
as (
select 'DATA1','DATA2','DATA1','DATA4','DATA1' from dual union all
select 'DATA2','DATA4','DATA2','DATA3','DATA3' from dual union all
select 'DATA3','DATA5','DATA3','DATA2','DATA5' from dual union all
select 'DATA5','DATA1','DATA4','DATA1','DATA2' from dual
)
select decode(lv,1,'A',2,'B',3,'C',4,'D',5,'E') gb
, nvl(max(decode(data,'DATA1',1)),0) data1
, nvl(max(decode(data,'DATA2',1)),0) data2
, nvl(max(decode(data,'DATA3',1)),0) data3
, nvl(max(decode(data,'DATA4',1)),0) data4
, nvl(max(decode(data,'DATA5',1)),0) data5
from
(
select lv, decode(lv,1,a,2,b,3,c,4,d,5,e) data
from t,
(select level lv from dual connect by level <=5)
)
group by lv
order by gb
by swlee
[2017.02.20 15:12:15]
with t(a,b,c,d,e)
as (
select 'DATA1','DATA2','DATA1','DATA4','DATA1' from dual union all
select 'DATA2','DATA4','DATA2','DATA3','DATA3' from dual union all
select 'DATA3','DATA5','DATA3','DATA2','DATA5' from dual union all
select 'DATA5','DATA1','DATA4','DATA1','DATA2' from dual
)
select GB
, COUNT(CASE WHEN DT = 'DATA1' THEN 1 END) DATA1
, COUNT(CASE WHEN DT = 'DATA2' THEN 1 END) DATA2
, COUNT(CASE WHEN DT = 'DATA3' THEN 1 END) DATA3
, COUNT(CASE WHEN DT = 'DATA4' THEN 1 END) DATA4
, COUNT(CASE WHEN DT = 'DATA5' THEN 1 END) DATA5
from (
select decode(lv,1,a,2,b,3,c,4,d,5,e) dt
, decode(lv,1,'A',2,'B',3,'C',4,'D',5,'E') GB
from t, (select level lv from dual connect by level <= 5))
GROUP BY GB
ORDER BY GB;
) 버튼을 클릭하여 작성 하시면 됩니다.