;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id,recordid ORDER BY code) AS RN
FROM #tmpTable
)
SELECT
Id,
RecordId,
MAX(CASE WHEN RN=1 THEN Code END) AS Code1,
MAX(CASE WHEN RN=1 THEN [Description] END) AS Desc1,
MAX(CASE WHEN RN=2 THEN Code END) AS Code2,
MAX(CASE WHEN RN=2 THEN [Description] END) AS Desc2
FROM
cte
GROUP BY
Id,
RecordId;