Concatenate Rows in SQL Server

In SQL Server if you would like to concatenate row to 1 single string, with using stuff, XML path aggregation, alternatively we can use T/SQL program.

I am preferring the TSQL cause of few reasons, first reason as before i was using XML Path aggregation and while using that method string truncation occurs at some limit. XML Path/stuff was not able to aggregate or concatenate all rows from table in single row.

Either we need to limit the number of rows while using above way.

in TSQL we can handle this situation, in that way we can play with length of charaters that need to concatenate.

How to use below TSQL:

first you guys need to populate your data in below table format, table name and column name should be exactly same.

Table Name - Table_Group

Column Name 1 - Column_id (will be using for group by )

Column Name 2 - column_text (that need to concatenate)

IF exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'data' AND TABLE_SCHEMA = 'dbo')

Drop table dbo.data;

create table data (id int, content nvarchar(max))

Declare

@out1 VARCHAR(max),

@Query_Part1 VARCHAR(max), 

@Query_Part2 VARCHAR(max), 

@Query_Part3 VARCHAR(max),

@Query_Part4 VARCHAR(max),

@Query_Final VARCHAR(max),

@row_num int

Declare

@column_id VARCHAR(400), 

@column_text VARCHAR(max) ,

@n int

set @Query_Part1 = '';

set @Query_Part2 = '';

set @Query_Part3 = '';

set @Query_Part4 = '';

set @Query_Final = '';

set @n = 0;

set @row_num = 0;

DECLARE CURSOR_C1 CURSOR FOR SELECT distinct column_id  FROM Table_Group  order by 1

OPEN CURSOR_C1

FETCH NEXT FROM CURSOR_C1 INTO @column_id 

WHILE @@FETCH_STATUS=0

BEGIN

DECLARE CURSOR_C2 CURSOR FOR 

SELECT column_text, row_number() over (order by column_id ) rn FROM Table_Group where column_id  = @column_id order by 2

OPEN CURSOR_C2

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

WHILE @@FETCH_STATUS=0

BEGIN

IF isnull(LEN(@Query_Part2),0) < 1 AND (isnull(LEN(@Query_Part1),0) + isnull(len(@column_text),0)  ) < 32767 

set @Query_Part1 = @Query_Part1 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10) +@column_text 

ELSE IF isnull(LEN(@Query_Part3),0) < 1 AND (isnull(LEN(@Query_Part2),0) + isnull(len(@column_text),0) ) < 32767 

set @Query_Part2 = @Query_Part2 +char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE IF isnull(LEN(@Query_Part4),0) < 1 AND (isnull(LEN(@Query_Part3),0)  + isnull(len(@column_text),0)) < 32767 

set @Query_Part3 = @Query_Part3 + char(10)+ '-Line  ' +cast(@row_num as nvarchar)+ ' - '+char(10)  +@column_text 

ELSE

set @Query_Part4 = @Query_Part4 +char(10)+ '-Line ' +cast(@row_num as nvarchar)+ ' - ' +char(10) +@column_text 

set @Query_Final = isnull(@Query_Part1,'')+isnull(@Query_Part2,'')+isnull(@Query_Part3,'')+isnull(@Query_Part4,'')

--print @Query_Final

FETCH NEXT FROM CURSOR_C2 INTO @column_text , @row_num

END

CLOSE CURSOR_C2;

DEALLOCATE CURSOR_C2;

insert into data values ( @column_id , @Query_Final) ;

set @Query_Final = ''

set @Query_Part1 = ''

set @Query_Part2 = ''

set @Query_Part3 = ''

set @Query_Part4 = ''

FETCH NEXT FROM CURSOR_C1 INTO @column_id;

set @row_num = 0 ;

END

CLOSE CURSOR_C1;

DEALLOCATE CURSOR_C1;

select *from data