sql server question

[EternalOptimist]

sql server 2005

I have two tables

table A has columns id, stepvalue, status
table B has many columns id, 'F' & stepvalue ,,,,,,,,,


I want to put A.status into B.F??


I tried this using two SP's , a wrapper with a cursor plus one that executed each stepvalue but it was way too slow


A has 6k rows
B has 100

[bored]

I'm no SQL Server expert but can you try an inner join:

UPDATE b SET f=a.status FROM b INNER JOIN a ON b.id = a.id

[MrRobin]

This will only work if your IDs are not duplicated

update B 
inner join
(select A.status, A.id
from A inner join B
on A.id = B.id) derivedT
on derivedT.id = B.id
set F = derviedT.status

[Spacecadet]

finally figured out what you meant...

you want the F column in table B to hold the concatenated statuses from the A table matched by ID and Step value
ie F = status1|status2|status3......

before i go into any SQL.... why the funk do you want to do this? it goes against every single RDBMS principle!

[EternalOptimist]

This will only work if your IDs are not duplicated

update B 
inner join
(select A.status, A.id
from A inner join B
on A.id = B.id) derivedT
on derivedT.id = B.id
set F = derviedT.status

the whole point is that I dont know f

f is determined by one of the columns in table A

[MrRobin]

the whole point is that I dont know f

f is determined by one of the columns in table A

Yes... 'F' is determined by 'Status' in the other table, right? So you select all the statuses in A that also have an ID in table B and then match them up.

[EternalOptimist]

Yes... 'F' is determined by 'Status' in the other table, right? So you select all the statuses in A that also have an ID in table B and then match them up.

no, f is the column name

[MrRobin]

Yes, I know it is.

You're not making much sense I'm afraid...

[EternalOptimist]

finally figured out what you meant...

you want the F column in table B to hold the concatenated statuses from the A table matched by ID and Step value
ie F = status1|status2|status3......

before i go into any SQL.... why the funk do you want to do this? it goes against every single RDBMS principle!

I know its all screwy. Its a legacy system that has a pivot table, where the end table (b) has column names that correspond to a step value in table a.column.stepvalue

eg

table A

ID stepvalue status

1, 1, y,
1, 3, y,
1 , 4 , y,
2, 1, n,
2, 3, y,


table b

ID F01 F02 F03 F04===>
1, y, y, , y,
2, n , , y, ,



the problem is, I cant guarantee that the columns are there
plus using a wrapper and a cursor is way too slow

I am working on pivoting A right now, to get the b@astards to line up....unless anyone has a slicker idea

[Spacecadet]

which SQL Server version are you using?

run this if you're not sure what you've connected to:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

If you're using 2005 then there are pivot functions that can help