declare @cUserName nvarchar(50) set @cUserName = 'danielm' select a.iIpoID, a.cGroupDealID, b.cIpoStatus, a.dTargetLiveDate , a.cCustNo, a.cCompany , MAX(CASE WHEN ipo_dsiContact.cDsiContType = 'IS' THEN cName ELSE '' END) AS cNameIS , MAX(CASE WHEN ipo_dsiContact.cDsiContType = 'TSS' THEN cName ELSE '' END) AS cNameTSS , MAX(CASE WHEN ipo_dsiContact.cDsiContType = 'CFA' THEN cName ELSE '' END) AS cNameCFA , c.cUserName, c.cPermission, a.dCreate from ipo a join lookup_ipoStatus b on a.iIpoStatus = b.iIpoStatus join ipo_permission c on a.iIpoID = c.iIpoID LEFT JOIN ipo_dsiContact ON a.iIpoID = ipo_dsiContact.iIpoID where a.cUserName = @cUserName and Year(dCreate) = 2007 GROUP BY a.iIpoID, a.cGroupDealID, b.cIpoStatus, a.dTargetLiveDate , a.cCustNo, a.cCompany , c.cUserName, c.cPermission, a.dCreate order by a.iIpoId DESC