How can I output a record as a string with the fields separated by comma's in SQL?

I'm using SQL Server 2000 and I want to return a string like the following from this table:

Field开发者_高级运维1 | Field2 | Field3  

ABC    |  123   | abc  
DEF    |  456   | def

Output Desired:

"ABC,123,abc"

"DEF,456,def"

I believe I can do this by simple concatenation, but it feels messy. What is the best way to do this?

NOTE: This is a simple example, the actual use has 9+ fields and may have nulls.

try:

select
    '"'
        +ISNULL(CONVERT(varchar(1000),Field1),'null')
        + ',' + ISNULL(CONVERT(varchar(1000),Field2),'null')
        + ',' + ISNULL(CONVERT(varchar(1000),Field3),'null') 
        +'"' AS ResultColumn
    from MyTable

just adjust the 1000 in each CONVERT(varchar(1000), as necessary

You can use coalesce or simple concatenation as you mentioned.

Select Field1 + ','+ Field2 + ',' + Field3

Beware of null values through, if Field1,2, or 3 is null, you will get a Null result, this behavior can be changed but is not recommended.

Hmm, why do you search for a more complex answer than concatenation? If you want something "cleaner", then do the concatenation fro your code calling the DB server, that way you do not put the CPU burden on the server to concatenate.

One thing no one has mentioned that always comes back to bite people in the butt is that if your delimiter (comma) is contained in a data field, then you will have extra commas and your data will be ambiguous. (ie, does "a,b,c,d" contain three fields, or four? If the second field actually contains "b,c" then your export is malformed).

The CSV format has been applied slightly different over time, so there is no standard way to handle this situation. The way I personally prefer to handle it is by enquoting the field values, and then escaping the quotes by repeating them. so for example, you have a record with the following data:

Field1: abc

Field2: def"1

Field3: 12,""3,b

The CSV record will be written as:

"abc","def""1","12,""""3,b"

That format avoids all possible ambiguity in field data.

Parsing it back is simple and deterministic as well. The other option is to use a field delimiter that you think can never happen in actual field data. This is a quick way to handle it, but then leaves a time bomb in your program for another programmer to fix later :-)

EDIT: If you are not going to be reading back the data, then, of course, you should see how the program reading the data handles importing data that contains delimiter characters as part of the field data that should not be interpreted as field delimiters.

The simplest is concatenation. Remember, keep it simple for the sake of understanding, readability and maintainability. No need for clever code.

select (Field1 + ',' + Field2 + ',' + Field3) as result
    from MyTable

Here's a link: http://msdn.microsoft.com/en-us/library/aa276862(SQL.80).aspx

Remark

When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is 'string'.

SELECT COALESCE(Field1,'''''') +','+ COALESCE(Field2,'''''') +',' +COALESCE(Field3,'''''') FROM Table1