Select multiple SQL rows into one row

Question

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

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

enter image description here

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

Refer this. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Sudhakar B, Jun 03 '12 at 04:04
@PeterMajeed: True, although it has never been asked in this particular form. — Andriy M, Jun 03 '12 at 04:19
@AndriyM: Right, and thanks for sharing - I never knew that your type of answer existed. — Peter Majeed, Jun 04 '12 at 13:41

score 5 · Accepted Answer · answered Jun 03 '12 at 04:15

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.

Select multiple SQL rows into one row

1 Answers1

Linked