Geek asks and answers

Select multiple SQL rows in one row

Possible duplicate:
Combine multiple lines into one text line?

Suppose I have a table with a name tblContractMail. An example of a table with the data below:

enter image description here

I need to write an 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 a way to get this output in a single query (I mean without declaring any variables)?

+5
source share
1 answer

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

, :

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

, . , . , , . int.

+5

More articles:


All Articles