Concatenate field titles based on conditions

I have attached a pic of a table that has 5 columns. I am needing to generate a 5th column of type 'nvarchar(MAX)' that is generated based on the value of the other 4 fields (either true or false). If the field value in columns 2-4 are true, then I would like to add (concatenate) the 'field name'  to the string.

Please provide a select statement that would generate this last column.

Thanks, Brad
4-19-2010-6-20-10-AM.png
brad_lubAsked:
Who is Participating?
 
ThomasianCommented:
Here you go
SELECT ID, Undeliverable, Local, Personal, email
     , REPLACE(RTRIM(CASE WHEN Undeliverable=1 THEN 'Undeliverable ' ELSE '' END
                     + CASE WHEN Local=1 THEN 'Local ' ELSE '' END
                     + CASE WHEN Personal=1 THEN 'Personal ' ELSE '' END
                    )
               ,' ', ', ')
       + '(' + email + ')' [ConcatField]
FROM myTable

Open in new window

0
 
Om PrakashCommented:
you can add case statements based on your need in below query
select id, undeliverable, local, personal, email, 
case when undeliverable= 1 and local= 1 and personal = 1 then 'undeliverable,local,persional (' + email + ')'  
case when undeliverable= 1 and local= 0 and personal = 1 then 'undeliverable,persional (' + email + ')'  
else ''  end [field you want] from table_name

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:

SELECT *, CASE 

		WHEN Undeliverable=1 AND Local=1 AND Personal=1 
		THEN 'Undeliverable, Local, Personal (' + email + ')'

		WHEN Undeliverable=1 AND Local=1 AND Personal=0 
		THEN 'Undeliverable, Local, Personal (' + email + ')'

		WHEN Undeliverable=1 AND Local=0 AND Personal=1 
		THEN 'Undeliverable, Local, Personal (' + email + ')'

		WHEN Undeliverable=0 AND Local=1 AND Personal=1 
		THEN 'Undeliverable, Local, Personal (' + email + ')'
..................

	END [New Field]

FROM YourTable

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
brad_lubAuthor Commented:
om_prakash_p:

I should have mentioned that my table has approximately 15-columns not just (3) as shown in my pic.

Can you think of an expression that would iterate over the various fields that would result in a string rather than defining all of the possible combinations?

Thanks!
0
 
Om PrakashCommented:
Above will not work, create temp table and update the values based on condition.

Please check the code below.

you can add more conditions to update data.
create table #temp (id int, undeliverable bit, local bit , personal bit , email varchar(100),customfield varchar(max))
insert into #temp values (1, 1,0,0,'test',null)

update #temp set customfield  = isnull(customfield,'')  + 'undeliverable' where undeliverable = 1
update #temp set customfield  = isnull(customfield,'')  + ' local' where local = 1

select * from #temp 

Open in new window

0
 
brad_lubAuthor Commented:
Thanks... this is what I needed!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.