dcsimg
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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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