help regarding sql query

Question

Hi , 

please tell me a sql query to perform this action

suppose following is the table i currently have :

cl1 | cl2 | keywords
c1 | cc1| 
c1 | cc2| 
d1 | dd1| 
d1 | dd2| 

suppose following is the table i want

cl1 | cl2 | keywords
c1 | cc1| cc1,cc2
c1 | cc2| cc1,cc2
d1 | dd1| dd1,dd2
d1 | dd2| dd1,dd2

Answer 1

Simply use the concatenate (+) operator with either CASE or COALESCE; for example:

declare @test table
( cl1 varchar(5), cl2 varchar(5), keywords varchar(9));
insert into @test(cl1, cl2)
select 'c1', 'cc1' union all select 'c1', 'c2' union all
select 'd1', 'dd1' union all select 'd1', 'dd2'
;
select
  cl1,
  cl2,
  coalesce(cl1, '') 
    + case when cl1 is not null and cl2 is not null
	       then ',' else '' end
    + coalesce(cl2, ''
  ) as keywords
from @test;
/* -------- Output: --------
cl1   cl2   keywords
----- ----- -----------
c1    cc1   c1,cc1
c1    c2    c1,c2
d1    dd1   d1,dd1
d1    dd2   d1,dd2
*/

Answer 2

Try:

declare @test table
( cl1 varchar(5), cl2 varchar(5));
insert into @test(cl1, cl2)
select 'c1', 'cc1' union all select 'c1', 'c2' union all
select 'd1', 'dd1' union all select 'd1', 'dd2'
;

select T.cl1, cl2, STUFF((select ',' + cl2 from @test T2 where T2.cl1 = T.cl1 order by cl2 for xml path('')),1,1,'') as  Keywords
from @test T

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Bless you, Naomi; thank you for picking me up.

:)

Another possibility:

declare @test table
( cl1 varchar(5), cl2 varchar(5), keywords varchar(9));
insert into @test(cl1, cl2)
select 'c1', 'cc1' union all select 'c1', 'cc2' union all
select 'd1', 'dd1' union all select 'd1', 'dd2' union all
select null, 'dd1' union all select 'd1', null union all
select null, null
;
select
  cl1,
  cl2,
  min(cl2) over(partition by cl1)
    + case when max(cl2) over(partition by cl1)
	          > min(cl2) over(partition by cl1)
		   then ',' + max(cl2) over(partition by cl1)
		   else '' end
  as Keywords
from @test;
/* -------- Output: --------
cl1   cl2   Keywords
----- ----- -----------
NULL  dd1   dd1
NULL  NULL  dd1
c1    cc1   cc1,cc2
c1    cc2   cc1,cc2
d1    dd1   dd1,dd2
d1    dd2   dd1,dd2
d1    NULL  dd1,dd2
*/