데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

문제 이해가 잘가지않네여

mainQuestionNo 에 subQuestionNo 각 고유로 존재해야 되는게 아닌지여?

제가 생각하는게 맞다면

select tempAppNo, mainQuestionNO,
case when answer1 is null then 'N' else answer1 end answer01,
case when answer2 is null then 'N' else answer2 end answer02,
case when answer3 is null then 'N' else answer3 end answer03,
case when answer4 is null then 'N' else answer4 end answer04,
case when answer5 is null then 'N' else answer5 end answer05,
case when answer6 is null then 'N' else answer6 end answer06,
case when answer7 is null then 'N' else answer7 end answer07,
case when answer8 is null then 'N' else answer8 end answer08
from
(
select tempAppNo, mainQuestionNO,
max(answerA1) as answer1,
max(answerA2) as answer2,
max(answerA3) as answer3,
max(answerA4) as answer4,
max(answerA5) as answer5,
max(answerA6) as answer6,
max(answerA7) as answer7,
max(answerA8) as answer8
from
  (
  select
  tempAppNo, mainQuestionNO,
  case when subQuestionNO = 0 then answer end as answerA1,
  case when subQuestionNO = 1 then answer end as answerA2,
  case when subQuestionNO = 2 then answer end as answerA3,
  case when subQuestionNO = 3 then answer end as answerA4,
  case when subQuestionNO = 4 then answer end as answerA5,
  case when subQuestionNO = 5 then answer end as answerA6,
  case when subQuestionNO = 6 then answer end as answerA7,
  case when subQuestionNO = 7 then answer end as answerA8
  from btest
  ) t1
group by tempAppNo, mainQuestionNO
) t2

임진표(운가라)님이 2009-07-14 10:08에 작성한 댓글입니다.

select a.mainQuestionNo,
      isnull(max(a.quest00),'N') as quest00,
      isnull(max(a.quest01),'N') as quest01,
      isnull(max(a.quest02),'N') as quest02,
      isnull(max(a.quest03),'N') as quest03,
      isnull(max(a.quest04),'N') as quest04,
      isnull(max(a.quest05),'N') as quest05,
      isnull(max(a.quest06),'N') as quest06
from (
      select mainQuestionNo,
            case subQuestionNo when '0' then answer end as quest00,
            case subQuestionNo when '1' then answer end as quest01,
            case subQuestionNo when '2' then answer end as quest02,
            case subQuestionNo when '3' then answer end as quest03,
            case subQuestionNo when '4' then answer end as quest04,
            case subQuestionNo when '5' then answer end as quest05,
            case subQuestionNo when '6' then answer end as quest06
       from tttttt
      ) a
group by a.mainQuestionNo

결과는

1	Y	Y	Y	N	N	N	N
2	Y	Y	N	N	N	N	N
4	Y	N	N	N	N	N	N
5	N	N	N	N	N	N	N
6	N	N	N	N	N	N	N
7	N	N	N	N	N	N	N

중복되는건 잘 처리하면 될꺼같습니다.

유성만(tiptop)님이 2009-08-27 17:17에 작성한 댓글입니다.
이 댓글은 2009-08-27 17:22에 마지막으로 수정되었습니다.