devxlogo

More Complex String Manipulations

More Complex String Manipulations

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 
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist