Combine rows to columns using COALESCE

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 541
Suppose we have a table as under

declare @t table(somecolumn varchar(MAX))
insert into @t
select 'X' union all
select 'Y' union all
select 'Z' union all
select 'R' union all
select 'S'


We want the result to be

CombinedResult
X, Y, Z, R, S


We can achieve this with COALESCE

DECLARE @SomeColumnList VARCHAR(8000);
SELECT @SomeColumnList =
COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20))
FROM @t T1
SELECT CombinedResult = @SomeColumnList


We are merging the rows into columns by using the COALESCE function and then storing the result of every wor into the variable @SomeColumnList

Comments or Responses

Login to post response