SELECT convert(varchar,SSG_Rate.dbo.AutoYearMakeModel_Factor.vehicleYear,4) + '_' + SSG_Rate.dbo.AutoYearMakeModel_Factor.make + '_' + 
		SSG_Rate.dbo.AutoYearMakeModel_Factor.model as 'New_ID',
		 ssg_rate.dbo.AutoYearMakeModel_Factor.coverageTypeID, ssg_rate.dbo.AutoYearMakeModel_Factor.factor, SSG_Rate.dbo.CoverageType.type
		FROM ssg_rate.dbo.AutoYearMakeModel_Factor inner join [SSG_Rate].[dbo].[CoverageType] on
		SSG_Rate.dbo.AutoYearMakeModel_Factor.coverageTypeID = SSG_Rate.dbo.CoverageType.coverageTypeID 

SELECT New_ID,
MAX(CASE WHEN type='BI' THEN factor ELSE NULL END) AS BI,
MAX(CASE WHEN type='PD' THEN factor ELSE NULL END) AS PD,
MAX(CASE WHEN type='COLL' THEN factor ELSE NULL END) AS COLL,
MAX(CASE WHEN type='COMP' THEN factor ELSE NULL END) AS COMP,
MAX(CASE WHEN type='LOAN' THEN factor ELSE NULL END) AS LOAN,
MAX(CASE WHEN type='PIP' THEN factor ELSE NULL END) AS PIP,
MAX(CASE WHEN type='UMBI' THEN factor ELSE NULL END) AS UMBI,
MAX(CASE WHEN type='MED' THEN factor ELSE NULL END) AS MED
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY New_ID	ORDER BY ratingversionid	DESC) AS rn,
New_ID,	coverageTypeID,	ratingversionid,	factor,	type
FROM table
)t
WHERE rn=1
GROUP BY New_ID