How to concatenate all the rows of a single int column to get a single string?

Question

Table: I have a database table table_1 in SQL Server 2012 with data as:

CREATE TABLE table_1
  (
     name nvarchar(128) not null,
     state tinyint null,
     state_desc nvarchar(60) null
  );

INSERT INTO table_1
VALUES      ('text1',1,'ONLINE'),
            ('text2',0,'ONLINE'),
            ('text3',0,'ONLINE'),
            ('text4',0,'ONLINE'),
            ('TEXTTE',0,'ONLINE'),
            ('TEXTTEXT',0,'ONLINE'),
            ('EXTTEXT',0,'ONLINE'),
            ('TEXTex_EX_Ext',0,'ONLINE'),
            ('TEXTex_TEX_Ext',0,'ONLINE'),
            ('TEXTTEXTText',0,'ONLINE'),
            ('Texttextext',0,'ONLINE'),
            ('TextTextext',0,'ONLINE'),
            ('TEXTER',1,'ONLINE');

I want to select all the values in column state and concatenate them to get a single string.

So desired result is a single row and a single column with data as:

1000000000001

What I have tried: Using substring but it skips first row (1) and gives 13 rows (000000000001 in each row) instead of just 1.

Select
    substring(
        (
            Select ''+ST1.[state]  AS [text()]
            From dbo.table_1 ST1
            For XML PATH ('')
        ), 2, 1000) [state]
From dbo.table_1 ST2;

Is there any other way to do this?

I will not know the number of rows and I want to keep the sequence while concatenating. (First row should be first digit, second row second digit, etc)

It doesn't matter if the first row goes rightmost or leftmost after concatenation, just it needs to be consistent and in sequence.

Possible duplicate of [How to concatenate text from multiple rows into a single text string in SQL server?](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv) — Pseudohuman, Apr 10 '18 at 22:42

asmgx · Answer 1 · 2018-04-11T03:08:52.910

1

try using variable see this

declare @Str varchar(1000)

set @Str = ''

update table_1
set @Str = @Str + cast(state as varchar)

select  @Str

edited Apr 11 '18 at 03:08

answered Apr 10 '18 at 23:29

asmgx

7,328
15
82
143

score 1 · Answer 2 · answered Apr 10 '18 at 23:50

--Try this query

SELECT replace([state],',','')
FROM(
SELECT stuff( (SELECT ',' + CONVERT(VARCHAR(1000), ST1.[state])
               FROM table_1 ST1
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')
       AS [state]
)t

score 1 · Accepted Answer · answered Apr 11 '18 at 03:14

1

Your query is almost correct. You just do not need the part with substring. Also I suggest you to order rows while concatenating with for xml path. Do you have some ID column? I have slightly modified your query:

select result = (
    Select ''+ST1.[state]  AS [text()]
    From dbo.table_1 ST1
    For XML PATH ('')
)

answered Apr 11 '18 at 03:14

uzi

4,118
1
15
22

Thank you for your suggestions @asmgx, @ASP and @uzi as they all work as expected for me. I finally decided to use @uzi suggestion for it's simplicity. And I am using the id field by inserting `order by [id]` before the row `For XML PATH ('')` in SQL. I hope that's how you suggested to order the rows. – 300 Apr 11 '18 at 05:11

How to concatenate all the rows of a single int column to get a single string?

3 Answers3