>>>>Given a SQL2008 table structure:
>>>>
>>>>Id, ColumnId, RowId
>>>>1 1 1
>>>>2 1 2
>>>>3 2 1
>>>>4 2 2
>>>>
>>>>How do I convert it to:
>>>>
>>>>Row, Column1 Column2
>>>> 1 1 3
>>>> 2 2 4
>>>>
>>>>In case it's relevant the values are all GUIDS rather than ints and there will be more columns (and, obviously, rows)
>>>>(Sorry about the spacing....)
>>>
>>>

>>>DECLARE @test TABLE (Id uniqueidentifier default newID(), ColumnId int, RowId INT)
>>>INSERT @test ( ColumnId, RowId ) VALUES  ( 1,1 )
>>>INSERT @test ( ColumnId, RowId ) VALUES  ( 1,2 )
>>>INSERT @test ( ColumnId, RowId ) VALUES  ( 2,1 )
>>>INSERT @test ( ColumnId, RowId ) VALUES  ( 2,2 )
>>>SELECT * FROM @test
>>>
>>>SELECT RowID, 
>>>	cast([1] as uniqueidentifier) as Column1,
>>>	cast([2] as uniqueidentifier) as Column2
>>>from (select cast(Id AS BINARY(16)) AS id, columnId, rowID FROM @test) tmp
>>>pivot 
>>>(
>>>  max( id )
>>>  for columnID in ([1],[2])
>>>) AS pvt
>>>

>>>
>>>But think twice, should you do this. Probably there is a better way using apply or Linq.
>>>
>>>PS: A linq version (same table as above):
>>>

>>>PivotTestTable.AsParallel()
>>> .GroupBy (pt => pt.RowId )
>>>	.Select (pt => new {
>>>	RowId = pt.Key,
>>>	Column1 = pt.SingleOrDefault (p => p.ColumnId==1).Id,
>>>	Column2 = pt.SingleOrDefault (p => p.ColumnId==2).Id
>>>	})
>>>

>>
>>Thanks Cetin. That looks great. I would have to build the query on the fly though since the number of columns will not be known in advance. In that respect Naomi's script which dynamically handles the number of columns works well.
>>
>> OTOH the data I'm really trying to get won't be the Id field but another field in the source table holding varbinary. Getting that in the Linq is simple - but probably not so simple in SQL.....
>
>Maybe you would want to mix Linq with some classic C#? I played with it a bit tonight. I think this mught work for you:
>

>                var data = dbContext.MyPivots.ToArray();
>
>	var rows = data.Select (mp => mp.RowId).Distinct().Select( (row,pos) => new {row,pos} );
>	var cols = data.Select (mp => mp.ColumnId).Distinct().Select( (col,pos) => new {col,pos} );
>	
>	Guid?[,] pivot = new Guid?[rows.Count(),cols.Count()];
>	
>	foreach (var record in data)
>	{
>	    int r = rows.Single( rr => rr.row==record.RowId).pos;
>	    int c = cols.Single( cl => cl.col==record.ColumnId).pos;
>		pivot[r,c] = (record.Id == null?null:record.Id); 
>	}
>
>

Cetin

Thanks Cetin,
I'll play around with variations but I'll probably create a concrete Row class that will hold a collection of Columns because at the end of the day I need something that will be WPF bindable.