ALTER VIEW v_kerusakan_gedung AS
SELECT 
    g.id,
    g.luas_atap as t_1_2,
    g.luas_langit as t_3_4_18,
    g.luas_dinding as t_5_9_17,
    g.jumlah_kusen as t_6_7_8_20,
    g.jumlah_ringbalk as t_10_11_19___1,
    g.jumlah_kolom as t_10_11_19___2,
    g.luas_lantai as t_12,
    g.panjang_fondasi as t_13,
    g.jumlah_instalasi_air as t_14,
    g.jumlah_instalasi_listrik as t_15,
    g.jumlah_instalasi_drainase as t_16,
    SUM(CASE WHEN k.sub_komponen_id = 1 THEN k.ukuran ELSE 0 END) as k_1,
    SUM(CASE WHEN k.sub_komponen_id = 2 THEN k.ukuran ELSE 0 END) as k_2,
    SUM(CASE WHEN k.sub_komponen_id = 3 THEN k.ukuran ELSE 0 END) as k_3,
    SUM(CASE WHEN k.sub_komponen_id = 4 THEN k.ukuran ELSE 0 END) as k_4,
    SUM(CASE WHEN k.sub_komponen_id = 5 THEN k.ukuran ELSE 0 END) as k_5,
    SUM(CASE WHEN k.sub_komponen_id = 6 THEN k.ukuran ELSE 0 END) as k_6,
    SUM(CASE WHEN k.sub_komponen_id = 7 THEN k.ukuran ELSE 0 END) as k_7,
    SUM(CASE WHEN k.sub_komponen_id = 8 THEN k.ukuran ELSE 0 END) as k_8,
    SUM(CASE WHEN k.sub_komponen_id = 9 THEN k.ukuran ELSE 0 END) as k_9,
    SUM(CASE WHEN k.sub_komponen_id = 10 THEN k.ukuran ELSE 0 END) as k_10,
    SUM(CASE WHEN k.sub_komponen_id = 11 THEN k.ukuran ELSE 0 END) as k_11,
    SUM(CASE WHEN k.sub_komponen_id = 12 THEN k.ukuran ELSE 0 END) as k_12,
    SUM(CASE WHEN k.sub_komponen_id = 13 THEN k.ukuran ELSE 0 END) as k_13,
    SUM(CASE WHEN k.sub_komponen_id = 14 THEN k.ukuran ELSE 0 END) as k_14,
    SUM(CASE WHEN k.sub_komponen_id = 15 THEN k.ukuran ELSE 0 END) as k_15,
    SUM(CASE WHEN k.sub_komponen_id = 16 THEN k.ukuran ELSE 0 END) as k_16,
    SUM(CASE WHEN k.sub_komponen_id = 17 THEN k.ukuran ELSE 0 END) as k_17,
    SUM(CASE WHEN k.sub_komponen_id = 18 THEN k.ukuran ELSE 0 END) as k_18,
    SUM(CASE WHEN k.sub_komponen_id = 19 THEN k.ukuran ELSE 0 END) as k_19,
    SUM(CASE WHEN k.sub_komponen_id = 20 THEN k.ukuran ELSE 0 END) as k_20
FROM gedung g
LEFT JOIN kerusakan k ON g.id = k.gedung_id
GROUP BY g.id, g.luas_atap, g.luas_langit, g.luas_dinding, 
         g.jumlah_kusen, g.jumlah_ringbalk, g.jumlah_kolom,
         g.luas_lantai, g.panjang_fondasi, g.jumlah_instalasi_air,
         g.jumlah_instalasi_listrik, g.jumlah_instalasi_drainase;