Using CASE WHEN with variable - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using CASE WHEN with variable

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-06-06 : 06:16:17

Hello

I'm producing a script to output a file containing data in a required format. Part of this script requires me to change a person id field to something else for a number of people.

I can do this no problem using a script like:

CASE WHEN person_id = '12345678' THEN '24681012'

However I have about 400 people to do and don't really want a 400 line script that will take me about a week to write....!!

Is there a way of doing this passing a variable??

I have created a table (TRANSFER) containing the old ID (OLD_ID) and a field containing the new id (NEW_ID), with all the appropriate data in.

How could I use this table with a variable to get the required results?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 06:27:35

Join with this TRANSFER table on id=old_id and use new_id in select list to get new id value.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-06 : 06:39:58

create table #t1(
ID int)
create table #t2(
ID int,
real_ID int)

insert into #t1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5

insert into #t2
select 4, 14 union all
select 5, 15

select coalesce (#t2.real_ID, #t1.ID)
from #t1
left join #t2
on #t1.ID = #t2.ID

1
2
3
14
15

hope that helps
Bjoern

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources