Select column from table and add ',' except last row

Question

In this

SELECT  field + ',' FROM table

I get something like this

1,
2,
3,

But I need to get

1,
2,
3

Last one should have no comma.

Answer 1

You should check this function

LIST()

Also this question may be duplicated you can check out the question below and see if some answer fit to your needs: How to concatenate text from multiple rows into a single text string in SQL Server

Answer 2

It appears Firebird allows you to limit rows with the rows keyword.

Assuming it can also be used in an inline view, you could run the following:

select case when x.field is not null
             then t.field
              else t.field  + ','
               end as field_alias
from tbl t
left join
        (
        select field
        from tbl
        order by field desc
        rows 1 to 1
        ) x
           on t.field = x.field
order by 1

Answer 3

If you are using mysql, this would return you a comma separated list (in one row) of all values in your_column in your_table:

  SELECT GROUP_CONCAT(your_column) FROM your_table

It defaults to using a comma but you can specify more options like SEPARATOR, DISTINCT and ORDER BY.

Answer 4

If there is a unique field , you can try this way to get the last row without comma.

SELECT 
CASE when isnull(B.field,'')='' THEN A.field+',' ELSE A.field END 
FROM [table] A
left join
(
    SELECT TOP 1 field FROM [table] ORDER BY unique_field DESC
)B ON A.field=B.field
ORDER BY A.unique_field

Answer 5

As mentioned above MySQL would return comma separated values by default, in the past I've changed the separator to a space -

GROUP_CONCAT(table_column SEPARATOR " ")