how to concatenate in ms sql server 2005

I have a table with columns s1,s2,s3,s4,s5,s6. I want to concatenate and seperate the columns with a space and semi colon only when the value exist in the column.e.g. s1 has value v1, s2 value v2, s3 null, s4, v4, s5 null, s6 valud v6. The out put should be like

v1 ; v2;  v4; v6

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
mmalik15Author Commented:
thanks for the comment but i don't how that example relates to my query. I have used query like this

select RecordID, Title, URL, [Publication Date], teaser, s1+'; '+s2+'; '+s3+'; '+s4+'; '+s5+'; '+s6 as msubjectgen from dbo.Duets

but the prob with the above is it also concatenates the null values which offcourse I don't want. Any further ideas
NorieAnalyst Assistant Commented:


COALESEC(s1, '')+'; '+COALESEC(s2, '')+'; '+COALESEC(s3, '')+'; '+COALESEC(s4, '')+'; '+COALESEC(s5, '')+'; '+COALESEC(s6, '')

For ISNULL just replace COALESCE.

Though I'm sure there are much better ways, perhaps.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mmalik15Author Commented:
coalesce is good but the problem i have is semicolon will be concatenated regardless if there is value for a column. I want to concatenate semicolon and space only when there is a value in the column
NorieAnalyst Assistant Commented:
Try ISNULL, and include the semi-colon.

ISNULL([s1]+';', '')+ ISNULL([s2]+';', '')+ ISNULL([s3]+';', '')+ ISNULL([s4]+';', '')+ ISNULL([s5]+';', '')+ ISNULL([s6]+';', '')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmalik15Author Commented:
thanks for the comment again. I am still getting semicolons when there are null values.e.g.
Urinary incontinence;Urological and genital disorders;;;;;
NorieAnalyst Assistant Commented:
Are you sure the values are null?

It works for me with some simple data.
mmalik15Author Commented:

Sorry imnorie I have checked again and yes you're right. The column values are blank but they don't say as having null. How can we amend the above query for blank values?

try this

ISNULL(nullif([s1], '')+';', '')+ ISNULL(nullif([s2],'')+';', '')+ ISNULL(nullif([s3],'')+';', '')+ ISNULL(nullif([s4],'')+';', '')+ ISNULL(nullif([s5],'')+';', '')+ [s6]

or using rtrim if necessary

ISNULL(nullif([rtrim(s1)], '')+';', '')...
mmalik15Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.