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.
| Author |
Topic |
|
Trudye
Posting Yak Master
169 Posts |
 Posted - 2008-04-16 : 18:20:42
|
| Hi Guys,I am converting Access queries to SQL. One query has a string of IIF statements that can concatenate several strings into one field. For instance: IIf([Hot Dogs]=-1,"* Wants a Hot Dog ","") & IIf([Hamburger]=-1,"*Wants a Hamburger ","") & IIf([mustard]=-1,"*add mustard ","") & IIf([fries]=-1,"*Include Fries ","") & IIf([Chips]=-1,"*Include Chips ","") Consequently the output could be: *Hamburger *add mustard *Include Chips I know I can use the ‘Case When Then’ statement but how do I concatenate? Thanx |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-16 : 18:33:48
|
| In T-SQL, you can concatenate with +.SELECT 'Tara' + ' Kizer'Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2008-04-17 : 09:31:39
|
| Guys, thanks so much for the feedback. I know I have to replace the IIF with a CASE statement what I can't figure out is how to concatenate the mulitple strings that can occur*Hamburger *add mustard *Include Chipscomes from 3 different When statements, where to I place the '+' sign. Thanx,Trudye |
|
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2008-04-17 : 11:29:14
|
| Thanx so much guys for all your input, I finally figures it out.case when[hot dogs] = -1 then '* Wants a Hot Dog ' else '' end +case when[Hamburger]=-1 then '*Wants a Hamburger ' else '' end +case when[mustard]=-1 then '*add mustard ' else '' end +case when[fries]=-1 then '*Include Fries ' else '' end + case when[Chips]=-1 then '*Include Chips ' else '' end Thanx again |
|
|
|
|
|
|
|
|
Concatenating using the IIF statement