How to concatenate the cells in a row?

Question

There are many questions how to concatenate multiple rows into a varchar, but can you concatenate all the cells in a row into a varchar?

Example : A table with 3 columns

| Id | FirstName | LastName |
| 1  | John      | Doe      |
| 2  | Erik      | Foo      |

return the following

"1, John, Doe"
"2, Erik, Foo"

You know which table you are working on.

Note 1 : Assume that you don't know the name of the columns when you write your query.
~~Note 2 : I would like to avoid dynamic SQL (if possible)~~

I don't think it's possible to avoid Dynamic SQL when you don't know what columns your table has. — Evaldas Buinauskas, Feb 27 '15 at 20:42
No, it's not possible to avoid dynamic SQL if you assume you don't know the column names. — Code Different, Feb 27 '15 at 20:42
What are you really trying to do here? Stuffing all the columns into a single result is an indication that there may be a better way to do whatever it is your trying to do. — Sean Lange, Feb 27 '15 at 20:45
@SeanLange I agree there might be a better way but this is not the question. Please stay on topic. — Gudradain, Feb 27 '15 at 21:03
Since you don't want to do it the better way you can use STUFF to make this happen. Here is an excellent article explaining how you can do this. http://www.sqlservercentral.com/articles/comma+separated+list/71700/ I can't imagine how this is useful to shove everything into a single value but whatever. — Sean Lange, Feb 27 '15 at 21:28
@SeanLange Thx for the link but you need to log in to watch that article... I guess you mean http://stackoverflow.com/questions/18870326/comma-separated-results-in-sql. I much prefer Coalesce for these solutions but this is for merging multiple rows, not multiple cells and hence is off topic too... — Gudradain, Feb 27 '15 at 21:39
heh well I would recommend setting up an account over there either way. That site is full of great information. And no I didn't mean anything like the link you posted. It is a combination of STUFF and FOR XML. I will toss together a basic example for you. — Sean Lange, Feb 27 '15 at 21:41

score 1 · Answer 1 · answered Feb 27 '15 at 20:51

Only thing I can think of is setting nocount to on outputting results to text instead of a grid using these parameters. That can be done without knowing amount of columns and avoiding Dynamic SQL.

Result to Text parameters

SET NOCOUNT ON;

;WITH Test (Id, FirstName, LastName)
AS (
    SELECT 1, 'John', 'Doe'
    UNION ALL
    SELECT 2, 'Erik', 'Foo'
)
SELECT *
FROM Test

Will return you this:

1,John,Doe
2,Erik,Foo

score 1 · Answer 2 · answered Feb 27 '15 at 22:06

Here is the basic version of this. Converting this to a dynamic sql solution when the columns are unknown is going to be very tricky. You will need to use sql to dynamically generate a query similar to this. Any table that doesn't have a primary key, or a unique index would be nearly impossible because you wouldn't know what column to use as your group by. It also becomes more tricky because you don't know what datatype(s) you are working with. You would also need to be certain to add some logic to handle single quotes and NULL. This is an interesting challenge for sure. If I have time this weekend I may try to work something up for the dynamic version of this.

with Something(Id, FirstName, LastName) as
(
    select 1, 'John', 'Doe' union all
    select 2, 'Erik', 'Foo'
)

select STUFF((select cast(s2.Id as varchar(5)) + ', ' + s2.FirstName + ', ' + s2.LastName
        from Something s2
        where s2.Id = s.Id
        for xml path('')), 1, 0, '') as Stuffed
from Something s
group by Id

How to concatenate the cells in a row?

2 Answers2