Suppose we have a report where we
want to show all the rows value of a cell in a single unit separated by a comma
(,) or '-' then we can use this coalesce function. Before sql server 2008
this function was not available.
First
Create a table; in this case, i have given a name test08 with
values given below
name
a
b
c
d
e
f
g
NULL
h
a
Now, write the
following line of code to merge the following rows of this table:
declare @var as
varchar(max)
declare @tt as
table
(
name varchar(max)
)
insert into
@tt
select
name fROM test08
set @var=''
select
@var=coalesce(case when @var ='' then +''+ rtrim(isnull(name,'NA'))-- this line
will check value exist or not
else @var +','+
rtrim(isnull(name,'NA'))-- if row value exist then marge eache row seperated by
comma(,)
end,', ')
from @tt
Print @var
-------------------------------------------------Or
--------------------------------------------------
DECLARE @var1
VARCHAR(1000)
SELECT @var1 =
COALESCE(@var1,'') + Name + ';'
FROM test08
SELECT @var1
OUT PUT :
a,b,c,d,e,f,g,NA,h,a
From the above output we get that same value are added to merge
the distinct row use this
declare @var as
varchar(max)
declare @tt as
table
(
name varchar(max)
)
insert into
@tt
select
name fROM test08 group by name
set @var=''
select @var=coalesce(case when @var ='' then
+''+ rtrim(isnull(name,'NA'))-- this line will check value exist or not
else @var +','+
rtrim(isnull(name,'NA'))-- if row value exist then marge eache row seperated by
comma(,)
end,', ')
from @tt
Print @var
--------------------------------OR---------------------------------------------
DECLARE @var1
VARCHAR(1000)
SELECT @var1 =
COALESCE(@var1,'') + Name + ';'
FROM test08
group by name
SELECT @var1
