如何连接单个int列的所有行以获取单个字符串？

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

表：我在SQL Server 2012中有一个数据库表table_1，数据如下：

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.

我尝试过：使用子字符串但跳过第一行（1）并给出13行（每行000000000001）而不是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.

在连接之后第一行是最右边还是最左边并不重要，只需它是一致的并且是顺序的。

3 个解决方案

#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:

您的查询几乎是正确的。你只需要子串的部分。另外，我建议你在连接xml路径时订购行。你有一些ID栏吗？我稍微修改了你的查询：

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

#2

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

#3

try using variable see this

尝试使用变量看到这个

declare @Str varchar(1000)

set @Str = ''

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

select  @Str

#1