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 <= @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--//////////////////////////////////////////////////////////
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may