Loop in sql , perform check and then concatinate

Webbo_1980 used Ask the Experts™
Lets say i wanted to build a string value up, based on the contents of a table e.g.

select * from tableA

would return results such as

ID | Name
1    bob  
2    tim    
3    Ed      

And say i wanted to populare a variable which loops through each row and stores/concatinate it value based on a simple look up via a case statement e.g.

For each loop i need to perform a case select e.g. in non sql it would look something like

dim value as string
for each row in collection
    select case  row.name
               case tim
                          value += row.name & 'isfromspain '
               case ed
                          value += row.name & 'isfromusa '  

                case bob
                           value += row.name & 'isfromUK'  

  end select
end for

so the output would be

bobisfromspan edisfromusa bobisfromuk

Obivously my real query is far more complex and the example above is made up, however is there a way to cycle through each row, perform a check, and then concatinate?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

you can do it with sql as

select id, ename,
  when ename = 'tim' then ' is from Spain'
  when ename = 'HK' then ' is from Turkey'
  when ename = 'chiu' then ' is from China'
  else ' is unknows ;)'
  end as txt
from tableA

and you can use ename & txt in your app...
Database Consultant
Top Expert 2009
declare @string varchar(max)

    select @string  = COALESCE(@string +'', '') + case (name )
               WHEN  'tim'
                          THEN 'isfromspain '
               WHEN  'ed'
                          THEN 'isfromusa '  

               WHEN  'bob'
                          THEN 'isfromUK'  END
    FROM TableA                      

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial