How to move from rows to columns

Transact-SQL
1

Creating a temp table to link to another query. Temp table looks like this
Id. Recordid code. Description

    1.     506.  Any text

    1.     507   Some other text

    1.     602.  More text

Want to look like this
Id recordid. Code1 desc1 code2 desc2

    1.    506 any text 507 some other text

    1.  602.   More text

The goal is to have 4 code and desc fields across. However, sometimes there are 2 and sometimes 5 per id and record id.

2 
;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;
3

Thanks so much. That worked perfectly!

4

The question now would be, do you understand how and why it works? Do you know what the technique is called?