2005년을 사용하시니까.

CROSS APPLY를 이용하셔서 과목별 문항수별 구하시면됨..

drop TABLE 과목코드
go

CREATE TABLE 과목코드(SUBJECT varchar(4) , 과목 VARCHAR(8),SU   INT)

INSERT 과목코드
SELECT 'AA', '국어',2 UNION ALL
SELECT 'BB','영어',3 UNION ALL
SELECT 'CC','수학(CC)',4

DROP TABLE 수험자테이블
GO
CREATE TABLE 수험자테이블(NAME VARCHAR(6),SUBJECT CHAR(2))
INSERT 수험자테이블
SELECT '이명박','AA' union all
SELECT '김영삼','BB' union all
SELECT '김대중','CC' union all
SELECT '김대중','AA'

 
 WITH CopyT AS
 (
 SELECT 1 NO
 UNION ALL
 SELECT NO+1 NO
 FROM CopyT
 )
 SELECT TOP 100
  NO INTO CopyT
FROM CopyT

SELECT  (CASE WHEN RES.N_SEQ = 1 THEN RES.NAME  ELSE '' END ) AS NAME
      , (CASE WHEN RES.N_S_SEQ = 1 THEN RES.SUBJECT  ELSE '' END )  AS SUBJECT
      , RES.NO
  FROM (
         SELECT   RA.NAME
      , RB.SUBJECT
      , RB.NO
      , ROW_NUMBER()  OVER  (partition BY  RA.NAME  ORDER BY RA.NAME , RA.SUBJECT  ,RB.NO ) N_SEQ
      , ROW_NUMBER()  OVER  (partition BY  RA.NAME ,RA.SUBJECT ORDER BY RA.NAME , RA.SUBJECT  ,RB.NO ) N_S_SEQ
    FROM 수험자테이블 AS RA INNER JOIN
    ( select a.SUBJECT , b.NO
      from  과목코드 a
     cross apply
    (select no from CopyT where no <= a.SU ) b  ) RB
   ON  RA.SUBJECT = RB.SUBJECT
  ) AS RES 
     ORDER BY RES.NAME, RES.SUBJECT  ,RES.NO