Let’s say you have a table like this:

 ID test-- ----1 Hello1 Mr.1 Gates2 I2 am2 the best

And you want a result group by each ID:

 1 Hello Mr. Gates2 I am the best

Here’s the T-SQL code for General Ranking:

 --//////////////////////////////////////////////////ALTER TABLE _MyTableADD Rank int NULLDrop table #fiddeclare @Start intdeclare @FID intselect @Start = 0select distinct ID into #fid from MyTableupdate t1 set @Start = Rank = case when t1.ID = @FID then @Start_ + 1 else1 end,@fid = t2.IDfrom MyTable t1 ,#fid t2 where t1.ID = t2.ID--select * from MyTable--///////////////////////////////////////////////////////////

Now, you will see how to achieve the desired output, but not the generalized solution:

 --//////////////////////////////////////////////////////////SELECT ID, COALESCE( MIN( CASE WHEN rank = 1 THEN test END) , '' ) + _' ' + COALESCE( MIN( CASE WHEN rank = 2 THEN test END) , '' ) + _' ' + COALESCE( MIN( CASE WHEN rank = 3 THEN test END) , '' ) + _' '  as testFROM MyTableGROUP BY ID;--////////////////////////////////////////////////////////ID test-- ----1 Hello Mr. Gates2 I am the best

Here is the generalized solution (after giving the ranking as above):

 --//////////////////////////////////////////////////////////DECLARE @MaxRank  intDECLARE @Counter  int--DROP TABLE #OUtputCREATE TABLE #Output(ID int, CText Varchar(6000) )INSERT INTO #Output SELECT ID, rtrim(test) as test FROM _MyTable WhereRank=1SELECT @MaxRank = Max(Rank) From MyTableSET @Counter = 2WHILE @Counter <= @MaxRankBEGINUPDATE A SET A.CText = RTRIM(A.Ctext) + ' ' + RTRIM(test)  _FROM #Output AINNER JOINMyTable B ON A.ID = B.ID WHERE B.Rank = @CounterSET @Counter = @Counter +1ENDSELECT * FROM #OUtput--//////////////////////////////////////////////////////////