>Got this - following the example on PIVOT
>
>It returns the result you're after - but I'm not exactly sure why
>
>

>declare @tmp Table
>	(	Id	int,
>		ColumnId	int,
>		RowId		Int
>		)
>
>insert into @tmp 
>select 1, 1, 1 
>union all
>select 2, 1, 2
>union all
>select 3, 2, 1
>union all
>select 4, 2, 2
>
>
>-- select * from @tmp
>
>/*
>Row, Column1 Column2
>1 1 3
>2 2 4
>*/
>select	RowId, [1], [2], [3], [4]
>	from (
>		select Id, RowId, ColumnId from @tmp  
>		) as SourceTable 
>	pivot
>	(
>		max(Id)
>		for ColumnId in ([1], [2], [3], [4] )
>	) as PivotTable 
>	order by RowId
>	
>

>
>

>1	1	3	NULL	NULL
>2	2	4	NULL	NULL
>

Your code is the same as

select	RowId, [1], [2], [3], [4]
	from @tmp -- no need for derived table in this case
	pivot
	(
		max(Id)
		for ColumnId in ([1], [2], [3], [4] )
	) as PivotTable 
	order by RowId