Need to concatenate values in a table by not using loop or cursor

Question

Need to concatenate values in a table by not using loop or cursor

SQL Guy 1

SSCoach

Points: 15786
More actions
December 18, 2020 at 2:20 pm

#3822060
We have a table with the following values for some column:
```
Column1
-------
    123
     71
   1844
    191
    ...
```
The results column should be like this:
For the 1st row we will leave the value as it is.
For the 2nd row we need to concatenate values from 1st and 2nd rows
For the 3rd row we need to concatenate the previous concatenated value and the current value, and so on...
So the result should look like this:
```
Column1 Column2
------- -------
    123 123
     71 123_71
   1844 123_71_1844
    191 123_71_1844_191
    ... ...
```
Column1 is integer data type, Column2 is varchar(max). And the solution cannot be a loop or a cursor
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11381 More actions · Answer 1

how do you know the order of the values to concatenate? You can use Stuff to accomplish this, but you'll need a way to know which ones to include. (i.e. 1844 is the 3rd value and not included in the first 2. If you provide DDL and Sample data, we can provide a solution that works for you

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 2

Here is DDL:

create table table1
(
col_to_order int identity(1,1),
column1 int,
column2 varchar(max)
)
go

insert into table1
(column1)
values
(123),
(71),
(1844),
(191)
go

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 3

And yes, each new row should accumulate all the previous one plus the current one.

Thanks

Mike01 SSChampion Points: 11381 More actions · Answer 4


Select t.column1, (SELECT STUFF( 
                               (       SELECT '_' + cast(t1.column1 as varchar(max))
                                       FROM table1 t1
                                       WHERE t1.col_to_order <= t.col_to_order  
                                       FOR XML PATH('') 
                               ), 
                               1, 
                               1,'' 
                             )) AS Columns_Concatenated 
  from table1 t

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQL Guy 1 SSCoach Points: 15786 More actions · Answer 5

SQL Guy 1

SSCoach

Points: 15786

December 18, 2020 at 5:11 pm

#3822138

Thanks Mike!

This is exactly what I wanted.