Concatenate Algorithm within a Select Statement

Question

Post reply

Concatenate Algorithm within a Select Statement

RJ-476420

SSCarpal Tunnel

Points: 4806
More actions
November 24, 2008 at 2:08 pm

#194400

I would like to concatenate about 3 columns in to a single column from a select statement query like,
select a, b, c, from table1 to select a+b+c from table1.
But, a+b+C should be delimited with a comma(,) in the resultset. for example if a = valuea, b = valueb, c = valuec then the query should return the results to the user as,
"valuea, valueb, valuec" as a single column.
my problem is what if any of these column is NULL then how would i eliminate the comma(,) in them. if any (a, b, or c) is NULL then they need not be concatenated & concatenate only not null values & there is no priority which one should be concatenated first among a, b or c.
I dont want to see any one of below in my resultset,
, valueb, valuec
valuea, , valuec
valuea, valueb,
& so on....
Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Does this test code help you out?

create table #TestTab (

Col1 varchar(10) null,

Col2 varchar(10) null,

Col3 varchar(10) null

);

insert into #TestTab

select 'First', 'Second', 'Third' union all

select 'Apple',null,'Banana';

select

isnull(Col1 + ', ', '') +

isnull(Col2 + ', ', '') +

isnull(Col3, '')

from

#TestTab;

drop table #TestTab;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Sorry, but the code I posted isn't quite right. If Col3 is null or Col2 and Col3 is null, you end up with a ',' at the end.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Okay, with a minor change, this should work:

create table #TestTab (

Col1 varchar(10) null,

Col2 varchar(10) null,

Col3 varchar(10) null

);

insert into #TestTab

select 'First', 'Second', 'Third' union all

select 'Apple',null,'Banana' union all

select 'Apple2',null,null;

select

isnull(Col1, '') +

isnull(', ' + Col2, '') +

isnull(', ' + Col3, '')

from

#TestTab;

drop table #TestTab;

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 4

yup. this won't work.

select 'First', 'Second', 'Third' union all

select 'Apple',null,'Banana' union all

select 'Vista', null, null union all

select null,'Water',null union all

select null,null,'Coke';

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, got it now!!

create table #TestTab (

Col1 varchar(10) null,

Col2 varchar(10) null,

Col3 varchar(10) null

);

insert into #TestTab

select 'First', 'Second', 'Third' union all

select 'Apple',null,'Banana' union all

select 'Apple2',null,null union all

select null,'Vista',null;

select

stuff(

isnull(', ' + Col1, '') +

isnull(', ' + Col2, '') +

isnull(', ' + Col3, ''), 1, 2, '')

from

#TestTab;

drop table #TestTab;

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 6

RJ-476420

SSCarpal Tunnel

Points: 4806

November 24, 2008 at 2:50 pm

#902774

Thanks, Lynn.

Yes. This is a solution.

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 7

Lynn Pettis (11/24/2008)
Sorry, but the code I posted isn't quite right. If Col3 is null or Col2 and Col3 is null, you end up with a ',' at the end.

Well, as long as it is only 3 columns, you could do a case statement. It is ugly, but it works. Another technique, which is also ugly but scales better for many columns is to use a CTE like:

if object_id('tempdb..#testtab') is not null

drop table #testtab;

create table #TestTab (

Col1 varchar(10) null,

Col2 varchar(10) null,

Col3 varchar(10) null

);

insert into #TestTab

select 'First', 'Second', 'Third' union all

select 'Apple',null,'Banana' UNION ALL

select null, 'num2', 'num3';

with cte1 as (

select

isnull(Col1 + ', ', '') +

isnull(Col2 + ', ', ', ') +

isnull(Col3 + ', ', ', ') as concatenated

from

#TestTab

)

select

concatenated,

replace(substring(concatenated, 1, len(concatenated) - 1), ', , ', ', ') as final

from cte1;

drop table #TestTab;

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/