Concatenating Multiple Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating Multiple Columns

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-07 : 17:40:57

Hello,
I want to concatenate multiple columns of data into just a single column but I only want to include the values if the columns are not null. I can't find an example of how to do this.

so if I have column1, column2, column3

test, test, null
test1,null null

I want my results in a 4th column to be
test,test
test1

but to leave off the null values.

In my select statement I currently have it concatenated like this:

h.riskfactorsstd1 + ',' + h.riskfactorsstd2 + ','+ h.riskfactorsstd3 as 'RiskofSTDs',

I just don't want to add it to the list if the value is Null.

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 18:01:09

here's one way


create table #t (a varchar(5), b varchar(5), c varchar(5));
insert #t (a, b) values ('test', 'test')
insert #t (a) values ('test1')

SELECT	a, b, c,
	a +
	case when b is not null then ',' + b else '' end +
	case when c is not null then ',' + c else '' end
FROM	#t

drop table #t

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-07 : 18:04:57

select
coalesce(h.riskfactorsstd1, '') +
case when h.riskfactorsstd2 is null and h.riskfactorsstd3 then ''
else ',' +
coalesce(h.riskfactorsstd2, '') +
case when h.riskfactorsstd3 is null then ''
else ',' + h.riskfactorsstd3
end
end

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-07 : 18:08:20

I like this way so I can have it in my select statement but I get this error:

Msg 4145, Level 15, State 1, Line 262
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

quote:
Originally posted by Bustaz Kool

select
coalesce(h.riskfactorsstd1, '') +
case when h.riskfactorsstd2 is null and h.riskfactorsstd3 then ''
else ',' +
coalesce(h.riskfactorsstd2, '') +
case when h.riskfactorsstd3 is null then ''
else ',' + h.riskfactorsstd3
end
end

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Thanks in Advance!
Sherri

sross81
Posting Yak Master

228 Posts

Posted - 2009-08-07 : 18:12:25

nevermind I found what was missing that created the error

quote:
Originally posted by sross81

I like this way so I can have it in my select statement but I get this error:

Msg 4145, Level 15, State 1, Line 262
An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

quote:
Originally posted by Bustaz Kool

select
coalesce(h.riskfactorsstd1, '') +
case when h.riskfactorsstd2 is null and h.riskfactorsstd3 then ''
else ',' +
coalesce(h.riskfactorsstd2, '') +
case when h.riskfactorsstd3 is null then ''
else ',' + h.riskfactorsstd3
end
end

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Thanks in Advance!
Sherri

Thanks in Advance!
Sherri

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 18:43:30

you can have either in your select statement. i just showed an example is all. either way is fine though

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-08 : 07:31:46

use coalesce or isnull condition than case statement
select a,b,c,isnull(a,'')+isnull(','+ b,'')+isnull(','+c,'')
from #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources