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
