Select multiple SQL rows into one row [duplicate]

Possible Duplicate:
Concatenate many rows into a single text string?

可能重复:将多行连接成一个文本字符串?

Suppose I have table named tblContractMail. Sample table with data given below:

假设我有一个名为tblContractMail的表。样本表包含以下数据:

I need to write a SQL query that produces the following output:

我需要编写一个产生以下输出的SQL查询:

'[e-mail];[e-mail];[e-mail]'

I know two possibilities:

我知道两种可能性:

DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + strContract FROM tblContractMail 
SELECT @str

and:

DECLARE @str varchar(4000)
SET @str = (SELECT strContract + ';' FROM tblContractMail FOR XML PATH(''))
SET @str = SUBSTRING(@str, 1, LEN(@str)-1)
SELECT @str

Is there any way to get this output in a single query (I mean with out declaring any variables)?

有没有办法在单个查询中获得此输出(我的意思是没有声明任何变量)?

1 个解决方案

#1

The first method relies on the variable, so the answer is no for the first one.

第一种方法依赖于变量,因此第一种方法的答案是否定的。

But you can easily use the second approach without a variable, only you need to modify it slightly:

但是你可以在没有变量的情况下轻松使用第二种方法,只需稍微修改它:

SELECT 
  SUBSTRING(
    (SELECT ';' + strContract FROM tblContractMail FOR XML PATH('')),
    2,
    2147483647
  )

As you can see, the separator goes before the item. As a result, you start cutting the string from the second character, omitting the leading semicolon. The length specifier doesn't have to be precisely the length minus one, you can specify any fairly big number, and the function will return everything from the second character to the end. In this case the maximum int value has been specified.

如您所见,分隔符位于项目之前。因此,您开始从第二个字符切割字符串,省略前导分号。长度说明符不必精确地为长度减去1,您可以指定任何相当大的数字,并且该函数将返回从第二个字符到结尾的所有内容。在这种情况下,已指定最大int值。