Complex grouping of strings in T-SQL

This tip will show you how to group strings based on one or more ID. This type of string manipulation is usually done in the front-end rather doing it in the back-end. Here, we will see how to accomplish the same functionality with T-SQL. 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 the result grouped by each ID:

1 Hello Mr. Gates 
2 I am the best 

Here is the T-SQL code for a multi-level ranking that gives a ranking for each string by taking each ID as one level:

--//////////////////////////////////////// 
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 

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

Now, here is 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; 
--//////////////////////////////////////// 

1 Hello Mr. Gates 
2 I am the best 

Next, we will go for a generalized solution. If you add another two IDs as shown below, then the above solution will not work.

ID test     Rank 
-- -----    --- 
1 Hello      1 
1 Mr.        2 
1 Gates      3 
2 I          1 
2 am         2 
2 the best   3 
3 thanks     1 
3 see you    2 
4 bye        1 
--//////////////////////////////////////// 
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 

--//////////////////////////////////////// 

In the above generalized solution we are updating each row in a loop. Instead of doing that, we can generate SQL dynamically and execute it. The following listing will show you how to build dynamic SQL with a case statement to accomplish the goal.

--//////////////////////////////////////// 
DECLARE @MaxRank  int 
DECLARE @Counter  int 
DECLARE @strSQL  varchar(8000) 
DECLARE @strSQL1  varchar(8000) 

SELECT @strSQL = '' 
SELECT @strSQL1 = '' 
SELECT @MaxRank = Max(Rank) From MyTable 
SET @Counter = 1 
WHILE @Counter <= @MaxRank 
BEGIN 
 IF @strSQL <> '' 
 SET @strSQL = @strSQL + ',' 
 SET @strSQL = @strSQL + ' MIN( CASE WHEN 
      RANK = ' + CAST(@counter as varchar(5)) + ' 
      THEN ISNULL(test,'''') END ) AS X' + CAST(@counter as varchar(5))

 SET @Counter = @Counter +1 
END 

SET @strSQL = 'SELECT ID, ' +  @strSQL + ' FROM MyTable GROUP BY ID' 
SET @Counter = 1 
--PRINT @strSQL 
--PRINT @Counter 
--PRINT @MaxRank 
WHILE @Counter <= @MaxRank 
BEGIN 
 IF @strSQL1 <> '' 
 SET @strSQL1 = @strSQL1 + ' + '' '' + ' 
 SET @strSQL1 = @strSQL1 + ' ISNULL(A.X' + CAST(@counter as varchar(5)) + ','''')' 
 SET @Counter = @Counter +1 
END 

--PRINT @strSQL1 

SET @strSQL1 = 'SELECT A.ID, ' + @strSQL1 + ' as FinalText FROM ( ' + @strSQL + ') A ' 
--//////////////////////////////////////// 

EXEC (@strSQL1) 
--PRINT @strSQL1 

Reader Feedback

Eva offers this comment: "You can reach the same results through the cursor without table alteration (a lot of times you are not allowed to do any alterations to existing tables). Like this:

CREATE TABLE #mytest (my_ID integer, Word varchar(20))
INSERT INTO #mytest VALUES (1,'Hello')
INSERT INTO #mytest  VALUES(1,'Mr.')
INSERT INTO #mytest  VALUES(1,'Gates!')
INSERT INTO #mytest  VALUES(2,'Today')
INSERT INTO #mytest  VALUES(2,'is')
INSERT INTO #mytest  VALUES(2,'a nice')
INSERT INTO #mytest  VALUES(2,'day!')


DECLARE @max_id int,
@cur_id int,
@new_id int,
@word varchar(20),
@phrase varchar(2000)
DECLARE phrase_cursor  cursor
FOR
SELECT my_ID, rtrim(ltrim(Word)) FROM #mytest 

OPEN phrase_cursor

SET @max_id = (select max(my_ID) from  #mytest)

SET nocount on

FETCH next from phrase_cursor into @cur_id,@word
SET @new_id =@cur_id
SET @phrase = cast(@new_id as varchar(3)) +'.'
WHILE @@FETCH_STATUS = 0 and @cur_id <= @max_id
BEGIN
IF @cur_id= @new_id 
SET @phrase = (@phrase + @word + ' ')
ELSE
BEGIN
                  SET @new_id =@cur_id
    SET @phrase = (@phrase + char(10) + char(13) +
cast(@new_id as varchar(3)) +'.' +@word+ ' ' )
END

FETCH NEXT FROM phrase_cursor INTO @cur_id,@word
END
CLOSE phrase_cursor
DEALLOCATE phrase_cursor
PRINT @phrase

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.