今回のようにデータが扱いにくかったり、点検時期の種類が増えたら列を追加しなければならないので定期点検毎に列を保つ構造はあまりよろしくないですね。
無理やりやるとするとinformation_schema
から列名を取得し、行列入れ替えを行って…
lang
1with colname AS (select
2 column_name,ordinal_position
3from
4 information_schema.columns
5where
6 table_name='test'
7order by
8 ordinal_position)
9 select id
10 , column_name
11 ,case when column_name like '%_inspection' then
12 case when v then '0'
13 else split_part(column_name,'_',1) end
14 else '' end
15 from (select id,generate_series(1, 39) AS seq ,
16 unnest(ARRAY["3_month_inspection"
17 , "6_month_inspection"
18 , "1_year_inspection"
19 , "2_year_inspection"
20 , "3_year_inspection"
21 , "4_year_inspection"
22 , "5_year_inspection"
23 , "6_year_inspection"
24 , "7_year_inspection"
25 , "8_year_inspection"
26 , "9_year_inspection"
27 , "10_year_inspection"
28 , "11_year_inspection"
29 , "12_year_inspection"
30 , "13_year_inspection"
31 , "14_year_inspection"
32 , "15_year_inspection"
33 , "16_year_inspection"
34 , "17_year_inspection"
35 , "18_year_inspection"
36 , "19_year_inspection"
37 , "20_year_inspection"
38 , "21_year_inspection"
39 , "22_year_inspection"
40 , "23_year_inspection"
41 , "24_year_inspection"
42 , "25_year_inspection"
43 , "26_year_inspection"
44 , "27_year_inspection"
45 , "28_year_inspection"
46 , "29_year_inspection"
47 , "30_year_inspection"
48 , "40_year_inspection"
49 , "50_year_inspection"
50 , "60_year_inspection"
51 , "70_year_inspection"
52 , "80_year_inspection"
53 , "90_year_inspection"
54 , "100_year_inspection"]) AS v
55 FROM test) as t
56 left join colname cn on t.seq = cn.ordinal_position;