Select Rows horizontally or select Rows in columns - SQL 2008

Source Table

Result 

SOLUTION

--Temp Table Delcaration--

--Temp table for output(Structure as you want it)
Declare @Output table(ID int identity,Name varchar(50),Col1 varchar(50),Col2 varchar(50),Col3 varchar(50),Col4 varchar(50),Col5 varchar(50))
--Temp output table as like source table with rownumber column added
Declare @TempOutput table(ID int identity,Name varchar(50),Game varchar(20),Position int,RowNum int)
--Table that has instance of the source table to keep source table data
Declare @SourceInstance table(ID int identity,Name varchar(50),Game varchar(20),Position int)
--Table to keep unique colum
Declare @UniqColumnTable table(ID int identity,Name varchar(50))

--Temp Variable--
--Index to increment the loop
Declare @Index int
--Count to find the number of uniqe data
Declare @count int
--Current Name to filter data
Declare @CurName varchar(50)


--Get the all data from the source tabel to out instance table
Insert into @SourceInstance
Select Student,Game,FinalPosition from [StudentParticipants]

--Get the unique columns from the table to our table
Insert into @UniqColumnTable
Select distinct(Name) from @SourceInstance

--Assign out tem variable with data from teh table
Set @Index = 1
Set @count = (Select COUNT(*) from @UniqColumnTable)

--Start the while loop
While @Index <= @count
BEGIN
--Set  the current filter colum to the variable
Set @CurName = (Select Name from @UniqColumnTable where ID = @Index)
--Get the Data to the temp output use the data from instance table and use 'Curuname' in where so we
--get one set of unique datas into the temp output table
Insert into @TempOutput
Select Name,Game,Position,ROW_NUMBER() over (Order by ID)  from @SourceInstance Where Name= @CurName

--If you have fixed you columns as 5 then keep 5 here or wat else your max column size
--This condition means that you have datas in row is less than or equal to your column count
IF ((Select COUNT(*) from @TempOutput) <= 5)
BEGIN
Insert into @Output
Select @CurName
,Case
WHEN (Select ID from @TempOutput where RowNum = 1) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 1)
ELSE
NULL
END
as Col1
,Case
WHEN (Select ID from @TempOutput where RowNum = 2) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 2)
ELSE
NULL
END
as Col2
,Case
WHEN (Select ID from @TempOutput where RowNum = 3) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 3)
ELSE
NULL
END
as Col3
,Case
WHEN (Select ID from @TempOutput where RowNum = 4) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 4)
ELSE
NULL
END
as Col4
,Case
WHEN (Select ID from @TempOutput where RowNum = 5) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 5)
ELSE
NULL
END
as Col5

--Delete the temp output otherwise your data will remain and keep repeating in you final output table
Delete @TempOutput
--Increament the index
Set @Index = @Index +1
END
ELSE
--IF you data in row is more than your colum count we can bring the columns downs with same unique name
BEGIN
Insert into @Output
Select @CurName
,Case
WHEN (Select ID from @TempOutput where RowNum = 1) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 1)
ELSE
NULL
END
as Col1
,Case
WHEN (Select ID from @TempOutput where RowNum = 2) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 2)
ELSE
NULL
END
as Col2
,Case
WHEN (Select ID from @TempOutput where RowNum = 3) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 3)
ELSE
NULL
END
as Col3
,Case
WHEN (Select ID from @TempOutput where RowNum = 4) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 4)
ELSE
NULL
END
as Col4
,Case
WHEN (Select ID from @TempOutput where RowNum = 5) IS NOT NULL Then (Select Game from @TempOutput where RowNum = 5)
ELSE
NULL
END
as Col5
--once the data up to ur column size is inserted, delete the inserted datas from the source instance tabele so we can fetch teh next row items
Delete @SourceInstance Where ID in(Select Top 5 ID from @SourceInstance where Name = @CurName)
--As before delete the tempoutput table
Delete @TempOutput
--The loppd will again go with same index value to fetech otheer value for the same unique column
END
END

-- finally display you out put
 Select * from @Output