Concatenate CASE Statements

Question

Post reply

Concatenate CASE Statements

Tek

SSCommitted

Points: 1697
More actions
December 11, 2012 at 7:37 am

#271411

Is there a method to concatenate CASE statements?
I have several fields that have values of 1 or 0. Each column name is the name of a product we have available. So I want you combine all these fields into one field, just like when one combines "FirstName + ' ' + LastName."
So I was hoping this would work:
CASE WHEN column1 = 1 THEN 'column1' ELSE '' END as column1 + ' ' + CASE WHEN column2 = 1 THEN 'column2' ELSE '' END as column2
Is this even possible?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You just need to get rid of the AS statements inside it.

CASE

WHEN column1 = 1 THEN 'column1'

ELSE ''

END

+ ' '

+ CASE

WHEN column2 = 1 THEN 'column2'

ELSE ''

END as ConcatenatedColumn

Like that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tek SSCommitted Points: 1697 More actions · Answer 2

GSquared (12/11/2012)
You just need to get rid of the AS statements inside it.
CASE
WHEN column1 = 1 THEN 'column1'
ELSE ''
END
+ ' '
+ CASE
WHEN column2 = 1 THEN 'column2'
ELSE ''
END as ConcatenatedColumn
Like that.

Thanks. This is working for me.

I ran into an issue though. Two of the columns are NTEXT. So when I use the code CASE WHEN Custom_3 = 1 THEN 'Easy Order' ELSE '' END as Product, I get a "The data types ntext and varchar are incompatible in the equal to operator" message. Is there a way around this?

Thanks

GSquared SSC Guru Points: 260824 More actions · Answer 3

If you're using SQL 2008 (as per the forum you posted in), then yes, there's a simple solution. Cast the columns to NVarchar(max). That will work in any version of SQL Server from 2005 forward.

If it's SQL 2000 or before, you'd have to cast to nvarchar(4000), and that might truncate data, depending on the actual data size in the NText columns.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 4

P.S.: Love the screen name you picked!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Tek SSCommitted Points: 1697 More actions · Answer 5

GSquared (12/11/2012)
If you're using SQL 2008 (as per the forum you posted in), then yes, there's a simple solution. Cast the columns to NVarchar(max). That will work in any version of SQL Server from 2005 forward.
If it's SQL 2000 or before, you'd have to cast to nvarchar(4000), and that might truncate data, depending on the actual data size in the NText columns.

Thanks. Before I read your solution, I found it.

here's what I used.

CASE CAST(Custom_10 as NVARCHAR(MAX)) WHEN '1' THEN 'Evolution'

GSquared SSC Guru Points: 260824 More actions · Answer 6

Yup. Looks like you've probably got it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon