December 11, 2012 at 7:37 am
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.
December 11, 2012 at 7:41 am
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
December 11, 2012 at 8:50 am
GSquared (12/11/2012)
You just need to get rid of the AS statements inside it.
CASEWHEN 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
December 11, 2012 at 8:55 am
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
December 11, 2012 at 8:56 am
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
December 11, 2012 at 9:00 am
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'
December 11, 2012 at 9:50 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply