行变列

需求：

1、考生成绩表KSHG

zkz                    kcdm bkcj

010101300001 1        60
010101300002 1        69
010101300002 2        80
010101300002 3        63
010101300002 4        74

.......................................

2、课程代码表 KCDM

kcdm  kcmc

1        马克思主义哲学原理                                             
2        邓小平理论概论                                                 
3        法律基础与思想道德修养 

4        毛泽东思想概论  

.............................

3、要做到

zkz                    kcdm1    kcmc1           kccj1    kcdm2   kcmc2           kccj2  kcdm3  kcmc3   kccj3 kcdm4  kcmc4   kccj4

010101300042   22      高等数学(工专)  79          2141 计算机网络技术   68    4729    大学语文  70     NULL    NULL   NULL

                                                                                        

SELECT KSCJ.zkz,
       SUM(CASE WHEN KSCJ.rn=1 THEN KSCJ.KCDM END) AS kcdm1,
       MAX(CASE WHEN KSCJ.rn=1 THEN KSCJ.KCMC END) AS KCMC1,
       SUM(CASE WHEN KSCJ.rn=1 THEN KSCJ.BKCJ END) AS kccj1,
       SUM(CASE WHEN KSCJ.rn=2 THEN KSCJ.KCDM END) AS kcdm2,
       MAX(CASE WHEN KSCJ.rn=2 THEN KSCJ.KCMC END) AS KCMC2,
       SUM(CASE WHEN KSCJ.rn=2 THEN KSCJ.BKCJ END) AS kccj2,
       SUM(CASE WHEN KSCJ.rn=3 THEN KSCJ.KCDM END) AS kcdm3,
       MAX(CASE WHEN KSCJ.rn=3 THEN KSCJ.KCMC END) AS KCMC3,
       SUM(CASE WHEN KSCJ.rn=3 THEN KSCJ.BKCJ END) AS kccj3,
       SUM(CASE WHEN KSCJ.rn=4 THEN KSCJ.KCDM END) AS kcdm4,
       MAX(CASE WHEN KSCJ.rn=4 THEN KSCJ.KCMC END) AS KCMC4,
       SUM(CASE WHEN KSCJ.rn=4 THEN KSCJ.BKCJ END) AS kccj4
 FROM 
(
select ZKZ,a.KCDM,BKCJ,HGSJ,b.KCMC, rn=row_number() over(partition by ZKZ order by a.KCDM)
from KSHG a LEFT JOIN [KCDM] b ON a.KCDM = b.KCDM
) AS KSCJ
GROUP BY KSCJ.ZKZ
ORDER BY KSCJ.ZKZ