데이터베이스 질문과 답변
안녕하세요.
설문 문제 테이블이 하나 있는데요..열 단위를 행단위로 바꾸고 싶은데.
이걸 쿼리로 뽑아내는 방법이 없나 하고 고민하고 있습니다.
테이블에 데이타는 아래와 같이 들어가 있는데.
| QUES | ANS1 | ANS2 | ANS3 | ANS4 | ANS5 | ANS6 | ANS7 | ANS8 | ANS9 | ANS10 |
| 설문내용1 | 예 | 아니요 | ||||||||
| 설문내용2 | 매우그렇다 | 약간그렇다 | 보통이다 | 약간그렇지않다 | 매우그렇지않다 | |||||
| 설문내용3 | 그렇다 | 보통이다 | 아니다 | |||||||
| 주관식설문내용 |
결과를 아래와 같이 뽑고 싶습니다.
| 설문내용1 |
| 예 |
| 아니요 |
| 설문내용2 |
| 매우그렇다 |
| 약간그렇다 |
| 보통이다 |
| 약간그렇지않다 |
| 매우그렇지않다 |
| 설문내용3 |
| 그렇다 |
| 보통이다 |
| 아니다 |
| 주관식설문내용 |
위와 같이 결과를 뽑는게 가능할까요?
참고로 오라클버전은 10g 입니다.
by 손님
[2012.03.14 09:54:07]
with t as
(select '설문내용1' ques, '예' ans1, '아니오' ans2, null ans3, null ans4, null ans5, null ans6, null ans7, null ans8, null ans9, null ans10 from dual
union all select '설문내용2', '매우그렇다', '약간그렇다', '보통이다', '약간그렇지않다', '매우그렇지않다', null, null, null, null, null from dual
union all select '설문내용3', '그렇다', '보통이다', '아니다', null, null, null, null, null, null, null from dual
union all select '주관식설문내용', null, null, null, null, null, null, null, null, null, null from dual
)
select regexp_substr(ans_t, '[^,]+', 1, lv) as ans_t
, lv
from (
select ques
, rtrim(ques||','||ans1||','||ans2||','||ans3||','||ans4||','||ans5||','||ans6||','||ans7||','||ans8||','||ans9||','||ans10, ',') ans_t
from t), (select level lv
from dual
connect by level <= (select max(length(cnt) - length(replace(cnt, ',')))+1
from (select rtrim(ans1||','||ans2||','||ans3||','||ans4||','||ans5||','||ans6||','||ans7||','||ans8||','||ans9||','||ans10, ',') cnt
from t)))
where regexp_substr(ans_t, '[^,]+', 1, lv) is not null
order by ques, lv;
by 손님
[2012.03.14 11:07:42]
select t.rn,c.no,decode(c.no,1,ques,2,ans1,3,ans2,4,ans3,5,ans4,6,ans5,7,ans6,8,ans7,9,ans8,10,ans9,11,ans10) qna
from (select rownum rn,t.* from t) t,(select level no from dual connect by level <= 11) c
)
where qna is not null
order by rn,no
by 손님
[2012.03.14 11:08:03]
select qna
from (
select t.rn,c.no,decode(c.no,1,ques,2,ans1,3,ans2,4,ans3,5,ans4,6,ans5,7,ans6,8,ans7,9,ans8,10,ans9,11,ans10) qna
from (select rownum rn,t.* from t) t,(select level no from dual connect by level <= 11) c
)
where qna is not null
order by rn,no
from (
select t.rn,c.no,decode(c.no,1,ques,2,ans1,3,ans2,4,ans3,5,ans4,6,ans5,7,ans6,8,ans7,9,ans8,10,ans9,11,ans10) qna
from (select rownum rn,t.* from t) t,(select level no from dual connect by level <= 11) c
)
where qna is not null
order by rn,no
by 손님
[2012.03.14 11:08:11]
with t as (select '설문내용1' ques, '예' ans1, '아니오' ans2, null ans3, null ans4, null ans5, null ans6, null ans7, null ans8, null ans9, null ans10 from dual union all select '설문내용2', '매우그렇다', '약간그렇다', '보통이다', '약간그렇지않다', '매우그렇지않다', null, null, null, null, null from dual union all select '설문내용3', '그렇다', '보통이다', '아니다', null, null, null, null, null, null, null from dual union all select '주관식설문내용', null, null, null, null, null, null, null, null, null, null from dual ) select regexp_substr(ans_t, '[^,]+', 1, lv) as ans_t , lv from ( select ques , rtrim(ques||','||ans1||','||ans2||','||ans3||','||ans4||','||ans5||','||ans6||','||ans7||','||ans8||','||ans9||','||ans10, ',') ans_t from t), (select level lv from dual connect by level <= (select max(length(cnt) - length(replace(cnt, ',')))+1 from (select rtrim(ques||','||ans1||','||ans2||','||ans3||','||ans4||','||ans5||','||ans6||','||ans7||','||ans8||','||ans9||','||ans10, ',') cnt from t))) where regexp_substr(ans_t, '[^,]+', 1, lv) is not null order by ques, lv;
by sendo
[2012.03.14 13:15:43]
답변 감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.