Select multiple SQL rows into one row
sql, sql-server, sql-server-2008
Solution
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.
Problem
Possible Duplicate: Concatenate many rows into a single text string? Suppose I have table named `tblContractMail`. Sample table with data given below: I need to write a SQL query that produces the following output: 'abc@akij.net;efg@akij.net;hjk@akij.net' 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)?