Rotasi Tabel di Sql Server 2005 (baris menjadi kolom)

hasilnya nanti akan seperti ini :




Syntax nya ada 2.. silahkan pilih salah satu :

1.)
SELECT NO_SOAL,
Q1= ISNULL((SELECT SKOR FROM COBA WHERE KD_RESPONDEN = 'R001' AND NO_SOAL = Q.NO_SOAL),0),Q2= ISNULL((SELECT SKOR FROM COBA WHERE KD_RESPONDEN = 'R002' AND NO_SOAL = Q.NO_SOAL),0),
Q3= ISNULL((SELECT SKOR FROM COBA WHERE KD_RESPONDEN = 'R003' AND NO_SOAL = Q.NO_SOAL),0), Q4= ISNULL((SELECT SKOR FROM COBA WHERE KD_RESPONDEN = 'R004' AND NO_SOAL = Q.NO_SOAL),0)
FROM COBA Q GROUP BY NO_SOAL

2.). jika format kolom integer maka gunakan script ini

select NO_SOAL = q.NO_SOAL,
SUM(CASE KD_RESPONDEN WHEN 'R001' THEN SKOR ELSE 0 END) as R001,
SUM(CASE KD_RESPONDEN WHEN 'R002' THEN SKOR ELSE 0 END) as R002,
SUM(CASE KD_RESPONDEN WHEN 'R003' THEN SKOR ELSE 0 END) as R003,
SUM(CASE KD_RESPONDEN WHEN 'R004' THEN SKOR ELSE 0 END) as R004
FROM coba q GROUP BY NO_SOAL

3. jika format kolom varchar  maka gunakan script ini

select NO_SOAL = q.NO_SOAL,
MAX(CASE KD_RESPONDEN WHEN 'R001' THEN SKOR ELSE '' END) as R001,
MAX(CASE KD_RESPONDEN WHEN 'R002' THEN SKOR ELSE '' END) as R002,
MAX(CASE KD_RESPONDEN WHEN 'R003' THEN SKOR ELSE '' END) as R003,
MAX(CASE KD_RESPONDEN WHEN 'R004' THEN SKOR ELSE '' END) as R004
FROM coba q GROUP BY NO_SOAL