Using case to concatenate strings

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)

Using case to concatenate strings

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-09 : 01:29:54

Hi I want to be able to concatenate some fields together. I think I can do this with a CASE statement but not quite sure how to add the comma and space between each word

e.g. from the table below I want to create a result that goes like this

col1 Col2
T401, Bread, Cheese, Tomato
T402, Ham
T403, Sausages, bread, Cheese
T404, Beer, Nuts
T405, Bread, olive Oil
T406, Steak, Cheese, fruit, Cake
T407, Tomato
T408, ice cream, pasta, oil

create table T (
recid varchar(10) null,
Food1 varchar(10) null ,
Food2 varchar(10) null ,
Food3 varchar(10) null ,
Food4 varchar(10) null
);

insert into T values ('T401', 'Bread', 'Cheese', 'Tomato', null);
insert into T values ('T402', null, 'Ham', 'null', null);
insert into T values ('T403', 'Sausages', null, 'Bread', 'Cheese');
insert into T values ('T404', 'Beer', 'Nuts', null, null);
insert into T values ('T405', 'Bread', null, 'olive oil', null);
insert into T values ('T406', 'Steak', 'Cheese', 'Fruit', 'Cake');
insert into T values ('T407', null, null, 'Tomato', null);
insert into T values ('T408', 'ice cream', null, 'pasta', 'oil');

I tried the following query

select recid,
case when isnull(food1,'')>'' then food1 +', ' else '' end +
case when isnull(food2,'')>'' then food2 +', ' else '' end +
case when isnull(food3,'')>'' then food3 +', ' else '' end +
case when isnull(food4,'')>'' then food4 else '' end
from t

But the problem is that if column4 is not he last column the results end with a comma in the wrong place

T401 Bread, Cheese, Tomato,
T402 Ham, null,
T403 Sausages, Bread, Cheese
T404 Beer, Nuts,
T405 Bread, olive oil,
T406 Steak, Cheese, Fruit, Cake
T407 Tomato,
T408 ice cream, pasta, oil

Sachin.Nand

2937 Posts

Posted - 2010-08-09 : 02:59:42

select recid,
reverse(stuff(reverse(
case when isnull(food1,'')>'' then food1 +',' else '' end +
case when isnull(food2,'')>'' then food2 +',' else '' end +
case when isnull(food3,'')>'' then food3 +',' else '' end +
case when isnull(food4,'')>'' then food4 +',' else '' end),1,1,''))
from t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

PavanKK
Starting Member

32 Posts

Posted - 2010-08-09 : 03:09:19

small modification(reverse is not required)

select recid,
STUFF(case when isnull(food1,'')>'' then ', '+food1 else '' end +
case when isnull(food2,'')>'' then ', '+food2 else '' end +
case when isnull(food3,'')>'' then ', '+food3 else '' end +
case when isnull(food4,'')>'' then ', '+food4 else '' end,1,1,'')
from T

KK :)

Sachin.Nand

2937 Posts

Posted - 2010-08-09 : 03:17:15

quote:
Originally posted by PavanKK

small modification(reverse is not required)

select recid,
STUFF(case when isnull(food1,'')>'' then ', '+food1 else '' end +
case when isnull(food2,'')>'' then ', '+food2 else '' end +
case when isnull(food3,'')>'' then ', '+food3 else '' end +
case when isnull(food4,'')>'' then ', '+food4 else '' end,1,1,'')
from T

KK :)

Thanks don't know what I was thinking

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-09 : 16:30:49

Thanks guys

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-09 : 17:13:07

No need CASE.

select recid,
stuff
(
coalesce(', ' + food1, '') +
coalesce(', ' + food2, '') +
coalesce(', ' + food3, '') +
coalesce(', ' + food4, ''), 1, 2, ''
) as list
from t

______________________

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-13 : 16:11:57

Can I ask a question so that I can understand.

I have tested the query below and it works.
select recid,
STUFF(case when isnull(food1,'')>'' then ', '+food1 else '' end +
case when isnull(food2,'')>'' then ', '+food2 else '' end +
case when isnull(food3,'')>'' then ', '+food3 else '' end +
case when isnull(food4,'')>'' then ', '+food4 else '' end,1,1,'')
from T

however if i look at the query it looks like it says.. if food1 is not empty then ", " + food1

It doesn't seem to make sense can someone explain for me

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources