Can the first table be transposed with one select sql to get the second table as shown? I have tried this below but it is far from perfect.

'SELECT table1.licence_no, table1.plateno, table1.desc,      
MAX(CASE WHEN axle_no = 1 THEN axle_dist ELSE NULL END) AS axle_dist_1,
MAX(CASE WHEN axle_no = 2 THEN axle_dist ELSE NULL END) AS axle_dist_2,
MAX(CASE WHEN axle_no = 3 THEN axle_dist ELSE NULL END) AS axle_dist_3,
MAX(CASE WHEN axle_no = 4 THEN axle_dist ELSE NULL END) AS axle_dist_4,
MAX(CASE WHEN axle_no = 5 THEN axle_dist ELSE NULL END) AS axle_dist_5,
from table1
group table1.licence_no, table1.plateno, table1.desc;'

CodePudding user response：

You can use dense_rank() in a subquery to enumerate the plateno (or plateno/desc combination) and then use conditional aggregation for those columns:

SELECT t1.licence_no,
       MAX(CASE WHEN seqnum = 1 THEN t1.plateno END) as plateno_1,
       MAX(CASE WHEN seqnum = 1 THEN t1.desc END) as desc_1,
       MAX(CASE WHEN seqnum = 2 THEN t1.plateno END) as plateno_2,
       MAX(CASE WHEN seqnum = 2 THEN t1.desc END) as desc_2,
       MAX(CASE WHEN axle_no = 1 THEN axle_dist ELSE NULL END) AS axle_dist_1,
       MAX(CASE WHEN axle_no = 2 THEN axle_dist ELSE NULL END) AS axle_dist_2,
       MAX(CASE WHEN axle_no = 3 THEN axle_dist ELSE NULL END) AS axle_dist_3,
       MAX(CASE WHEN axle_no = 4 THEN axle_dist ELSE NULL END) AS axle_dist_4,
       MAX(CASE WHEN axle_no = 5 THEN axle_dist ELSE NULL END) AS axle_dist_5
from (select1 t1.*,
              dense_rank() over (partition by license_no order by plateno) as seqnum
      from table1 t1
     ) t1
group t1.licence_no;