select CASE WHEN class LIKE '%seat%' THEN '+ per adult passenger seat over 7' ELSE REPLACE(class, 'base','') END AS Class, ISNULL(CAST(AAI AS VARCHAR),'n/a') AS [AAI (SUNCORP)],
ISNULL(CAST(ALLIANZ AS VARCHAR),'n/a') AS ALLIANZ ,
ISNULL(CAST(QBE AS VARCHAR),'n/a') AS QBE ,
ISNULL(CAST(RACQ AS VARCHAR),'n/a') AS RACQ
FROM (
select *
from
(
select period, rank, class, [insurer] , [gross_comm] AS premium
from [CTP_qld_import]
) src
pivot
(
SUM([premium])
for [insurer] in ([AAI], [ALLIANZ], [QBE], [RACQ])
) piv
) AS CTP
where period = 12 order by rank |