using variable name in columnname

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 2000 Forums

SQL Server Development (2000)

using variable name in columnname

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-04-08 : 16:21:19

EX.
I have a column in a table (t1).....code.

I need to move this data into another table(t2) that has several columns:
code1
code2
code3
code4...etc...

I put data into t2.code1 based on another field in t1 (list) so if list='1', I put the data in t2.code1, if list='4' then t2.code4 etc.

Want I want to know is can I set up a loop with a variable?
example
Declare @num int
Set @num=1
Where @num<=17
Update table2
set code(@num)=(select code from table 1 where list=@num)
set @num=@num+1
....etc....

The tricky part is making the variable part of the column name so the next update will happen to code2...

Thanks,
Eddie

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-08 : 16:29:33

oh dear lord ... another highly denormalized design. how open are you to changing it?

setBasedIsTheTruepath
<O>

eddie
Starting Member

45 Posts

Posted - 2002-04-08 : 16:35:37

I have no control over the design of these tables...and have a deadline of just a few days to get the procedure working...I can always just do several updates but I was looking for a cleaner way.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-08 : 16:49:24

How about:

UPDATE table2
SET code1=CASE T1.List WHEN 1 THEN code ELSE code1 END,
code2=CASE T1.List WHEN 2 THEN code ELSE code2 END,
code3=CASE T1.List WHEN 3 THEN code ELSE code3 END,
code4=CASE T1.List WHEN 4 THEN code ELSE code4 END,
code5=CASE T1.List WHEN 5 THEN code ELSE code5 END,
code6=CASE T1.List WHEN 6 THEN code ELSE code6 END,
code7=CASE T1.List WHEN 7 THEN code ELSE code7 END,
code8=CASE T1.List WHEN 8 THEN code ELSE code8 END,
code9=CASE T1.List WHEN 9 THEN code ELSE code9 END,
code10=CASE T1.List WHEN 10 THEN code ELSE code10 END,
code11=CASE T1.List WHEN 11 THEN code ELSE code11 END,
code12=CASE T1.List WHEN 12 THEN code ELSE code12 END,
code13=CASE T1.List WHEN 13 THEN code ELSE code13 END,
code14=CASE T1.List WHEN 14 THEN code ELSE code14 END,
code15=CASE T1.List WHEN 15 THEN code ELSE code15 END,
code16=CASE T1.List WHEN 16 THEN code ELSE code16 END,
code17=CASE T1.List WHEN 17 THEN code ELSE code17 END
FROM table2, table1 T1

This will update EVERY column regardless; however, if the List column doesn't match the value for the column number, it will update the column to its original value.

Now that you have (hopefully) a solution for this, you are entitled...indeed, requested...to beat the living shit out of whoever designed this table!

If YOU designed it, well, sorry, but you have to write "I will not design a table with repeating attributes" 100 times on the chalkboard!

Edited by - robvolk on 04/08/2002 16:51:10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources