Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Jan 4, 2002

More Complex String Manipulations


Let's say you have a table like this:
 
ID test
-- ----
1 Hello
1 Mr.
1 Gates
2 I
2 am
2 the best

And you want a result group by each ID:
 
1 Hello Mr. Gates
2 I am the best

Here's the T-SQL code for General Ranking:
 
--//////////////////////////////////////////////////ALTER TABLE _
MyTable
ADD Rank int NULL
Drop table #fid
declare @Start int
declare @FID int
select @Start = 0
select distinct ID into #fid from MyTable
update t1 set @Start = Rank = case when t1.ID = @FID then @Start_
 + 1 else
1 end,@fid = t2.ID
from 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 test
FROM MyTable
GROUP BY ID;
--////////////////////////////////////////////////////////

ID test
-- ----
1 Hello Mr. Gates
2 I am the best

Here is the generalized solution (after giving the ranking as above):
 
--//////////////////////////////////////////////////////////
DECLARE @MaxRank  int
DECLARE @Counter  int

--DROP TABLE #OUtput
CREATE TABLE #Output(ID int, CText Varchar(6000) )
INSERT INTO #Output SELECT ID, rtrim(test) as test FROM _
MyTable Where
Rank=1

SELECT @MaxRank = Max(Rank) From MyTable
SET @Counter = 2
WHILE @Counter <= @MaxRank
BEGIN
UPDATE A SET A.CText = RTRIM(A.Ctext) + ' ' + RTRIM(test)  _
FROM #Output A
INNER JOIN
MyTable B ON A.ID = B.ID WHERE B.Rank = @Counter
SET @Counter = @Counter +1
END


SELECT * FROM #OUtput
--//////////////////////////////////////////////////////////
Harinatha Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date