How do I join all values from multiple rows into a single row?

Question

Lets say I have the query:

SELECT Foo FROM Bar

Which returns

Foo
A
B
C

What I really what is:
Foo
A,B,C

So all of the values from all of the rows has been collapsed into a single row (the commas are optional).
Is there a way to use a select statement to do this because I do not want to use cursors?

score 2 · Answer 1 · answered Feb 25 '09 at 15:46

2

DECLARE @foos VARCHAR(4000)

SELECT @foos = COALESCE(@foos + ',', '') + Foo FROM Bar

SELECT @foos AS Foo

answered Feb 25 '09 at 15:46

LukeH

263,068
57
365
409

score 1 · Accepted Answer · answered Feb 25 '09 at 15:58

1

SELECT
    (   
    SELECT
      CASE
        WHEN ROW_NUMBER() OVER(ORDER BY bar) = 1 THEN ''
        ELSE ', '
      END + CAST(bar AS VARCHAR)
    FROM foo
    ORDER BY bar
    FOR XML PATH('')
    )

answered Feb 25 '09 at 15:58

Quassnoi

413,100
91
616
614

This looks "elegant" but quite slow – dance2die Feb 25 '09 at 16:01
Concatenated string do not tend to consist of millions records. For a dozen records which will most probable will there, it will work in a flash. – Quassnoi Feb 25 '09 at 16:03
In my case, this would only be applied to a few records and it could be used as a subquery in a select statement, which worked best for me. – Ross Goddard Feb 25 '09 at 16:30

score 0 · Answer 3 · answered Feb 25 '09 at 15:48

0

Ross,

this should get you started.

  DECLARE @r VARCHAR(8000)
  SELECT @r = (SELECT DISTINCT Foo + ', ' FROM Bar FOR XML PATH(''))
  IF @r IS NOT NULL AND @r <> '' SET @r = SUBSTRING(@r, 1, LEN(@r)-1)
  SELECT @r

answered Feb 25 '09 at 15:48

Lieven Keersmaekers

57,207
13
112
146

dance2die · Answer 4 · 2009-02-25T15:58:31.770

0

Try the following

declare @joined varchar(max)
set @joined = ''

select  @joined = @joined + IsNull(Foo + ',', '')
from    Bar (nolock)

--; Drop last "," if necessary
set @joined = substring(@joined, 1, len(@joined) - (case when len(@joined) > 0 then 1 else 0 end))
select  @joined as foo

edited Feb 25 '09 at 15:58

answered Feb 25 '09 at 15:53

dance2die

35,807
39
131
194

score 0 · Answer 5 · answered Feb 25 '09 at 16:05

0

 select max(a),max(b),max(c) from
 (
      select 'a' as a,null as b,null as c
      union
      select null,'b',null
      union
      select null,null,'c'
 ) as x

answered Feb 25 '09 at 16:05

Fredou

19,848
10
58
113

How do I join all values from multiple rows into a single row?

5 Answers5