带case语句的枢轴表

Question

我有这样的桌子。我正试图使它成为一个独特的记录。问题是，对于某些记录(糟糕的公共数据集)，传输线路号为231。所以我的解决方案是返回第一个运输机，2-9运输机，最后的运输机。但是，当我获得聚合中的聚合错误时，我很难理解如何将最大值合并到查询中。

​

​

Select manifesttrackingnumber,
 Min(Case TRANSPORTERLINENUMBER When 1 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'First Transporter',
 Min(Case TRANSPORTERLINENUMBER When 2 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 2',
 Min(Case TRANSPORTERLINENUMBER When 3 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 3',
 Min(Case TRANSPORTERLINENUMBER When 4 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 4',
 Min(Case TRANSPORTERLINENUMBER When 5 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 5',
 Min(Case TRANSPORTERLINENUMBER When 6 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 6',
 Min(Case TRANSPORTERLINENUMBER When 7 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 7',
 Min(Case TRANSPORTERLINENUMBER When 8 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 8',
 Min(Case TRANSPORTERLINENUMBER When 9 Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Transporter 9',
 Max(Case TRANSPORTERLINENUMBER When max(transporterlinenumber) Then concat(trim(TRANSPORTERNAME),' (',trim(TRANSPORTEREPAID),')') End) 'Final Transporter'
   From TRANSPORTER

Answer 1

实现一个子查询，以返回每个ID的最大编号。