CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlcoalesce
salvationishere
salvationishere

Reputation: 3511

How to coalesce many rows into one?

I am using SSMS 2008 R2 and am simply trying to coalesce many rows into one. This should be simple I think, but it is currently repeating data in each row. Consider:

create table test
(
Name varchar(30)
)
insert test values('A'),('B'),('C')
select * from test

select distinct Name, coalesce(Name + ', ', '')
from test

How can I rewrite this to achieve one row like: A,B,C

Upvotes: 2

Views: 32298

Answers (4)

Aram
Aram

Reputation: 5705

in the old days of SQL Server 7.0 and SQL Server 2000, I Used to do this with a variable and using COALESCE like this:

DECLARE @List VARCHAR(8000)

SELECT @List = COALESCE(@List + ',', '') + CAST(Color AS VARCHAR)
FROM   NameColorTable

SELECT @List

But after SQL Server 2005 and XPATH appearance, this the way I prefer to use:

SELECT STUFF(( 
    select ','+ cast(Color as nvarchar(255)) 
    from NameColorTable b 
    WHERE a.Name = b.Name 
    FOR XML PATH('') 
    ) 
    ,1,1,'') AS COLUMN2 
FROM NameColorTable a 
GROUP BY a.Name

I have a blog post about this here: https://koukia.ca/stuff-vs-coalesce-in-tsql-for-concatenating-row-values-aefb078536f8#.f4iggl22y

Upvotes: 1

Eralper
Eralper

Reputation: 6612

Here is the standard solution for concatenation using XML PATH()

SELECT
   STUFF(
     (
     SELECT
       ',' + Name
     FROM test
     FOR XML PATH(''),TYPE
     ).value('.','VARCHAR(MAX)'
     ), 1, 1, ''
   ) As list

Another option can be using the new SQL CONCAT() function introduced with SQL Server 2012. I used SQL Concat() in the below sample

declare @namelist nvarchar(max)
select @namelist = concat(isnull(@namelist+',',''), name) from test2
select @namelist

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74257

In SQL Server Transact-SQL, the easiest way to accomplish this is the following.

A table like this:

create table #foo
(
  id   int         not null identity(1,1) primary key clustered ,
  name varchar(32) not null ,
)

insert #foo (name) values ( 'a' )
insert #foo (name) values ( 'b' )
insert #foo (name) values ( 'c' )
insert #foo (name) values ( 'd' )
go

Can flattened using this seemingly dubious (but documented) technique:

declare @text varchar(max) = ''

select @text = @text
             + case len(@text)
                 when 0 then ''
                 else        ','
               end
             + t.name
from #foo t

select list_of_names = @text
go

yielding

list_of_names
-------------
a,b,c,d

Easy!

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

SELECT STUFF(( SELECT ', ' +  Name
               from #test
               FOR XML PATH(''), TYPE).
               value('.','NVARCHAR(MAX)'),1,2,'')


RESULT: A, B, C

I am sure this not how exactly your rows look that you are trying to concatenate, therefore see below for a slightly different data set and how you would go about doing this kind of operation on that

Test Data

create table #test
(
Id INT,
Name varchar(30)
)
insert #test values
(1,'A'),(1,'B'),(1,'C'),(2,'E'),(2,'F'),(2,'G')

Query

select t.Id
      , STUFF(( SELECT ', ' +  Name
               from #test
               WHERE Id = T.Id
               FOR XML PATH(''), TYPE).
               value('.','NVARCHAR(MAX)'),1,2,'') AS List
FROM #test t
GROUP BY  t.Id

Result Set

╔════╦═════════╗
║ Id ║  List   ║
╠════╬═════════╣
║  1 ║ A, B, C ║
║  2 ║ E, F, G ║
╚════╩═════════╝

Upvotes: 5

Related Questions