Refer to some value in the row & then replace it

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

SSIS and Import/Export (2005)

Refer to some value in the row & then replace it

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-30 : 11:40:58

Hello Everyone,

My subject line seems to be a bit confusing.

I have a table:
Message GradeID
Pass ABC12
Pass ABC1209
Pass ABC12
Fail DEF21
Fail DEF45
Not Sure RST39
Not Sure RST34
Not Sure RST39

But what I want the following logic in the new table that I will be creating.

If Message is Pass, then it should look for the Pass values in the GradeID's column and where it finds the entry with 12, it should enter that value in all the Pass GradeID columns.

If Message is Fail, then it should look for the Fail values in the GradeID's column and look for the entry which has 45 in it and then replace the whole value in all the Fail GradeID columns.

Similarly for not sure, it should look for value having 39 in the GradeID.

So, the final result will be like this:

Message GradeID
Pass ABC12
Pass ABC12
Pass ABC12
Fail DEF45
Fail DEF45
Not Sure RST39
Not Sure RST39
Not Sure RST39

So, all Pass will be = 12, All Fail will be = 45 and All Not Sure will be 39, which does not mean 12, 45 or 39 merely, it will look for the options where there is 12 and then take that whole value and replace it in that column.

Please help me achieve this complicated requirement using SSIS.
I will be very thankful to you.
I will be here to answer your questions, if my question is not clear here.

Thank you in advance.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-30 : 12:29:21

Your explanation does not match against expected output.

You say "If Message is Fail, then it should look for the Fail values in the GradeID's column and look for the entry which has 45 in it and then replace the whole value in all the Fail GradeID columns."

Then how did "Fail DEF21" become "Fail DEF45". It does not have 45 in it.

Can you please explain this part. Based on your sample data, looks like this is what you need?

Select messag,
CASE messag
WHEN 'Pass' THEN 'ABC12'
WHEN 'Fail' THEN 'DEF45'
WHEN 'Not Sure' THEN 'RST39'
END AS gradeid
from @t

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-03-30 : 13:17:01

quote:
Originally posted by vijayisonly

Your explanation does not match against expected output.

You say "If Message is Fail, then it should look for the Fail values in the GradeID's column and look for the entry which has 45 in it and then replace the whole value in all the Fail GradeID columns."

Then how did "Fail DEF21" become "Fail DEF45". It does not have 45 in it.

Can you please explain this part. Based on your sample data, looks like this is what you need?

Select messag,
CASE messag
WHEN 'Pass' THEN 'ABC12'
WHEN 'Fail' THEN 'DEF45'
WHEN 'Not Sure' THEN 'RST39'
END AS gradeid
from @t

Thank you for your reply.
I tried the CASE statement before posting my question on SQL Team, but initially I thought it will not work.
Now, when I relooked into it, I guess this should work.
I shall keep you posted if I still do not achieve what I wish.

Thanks once again for your prompt reply.

???? ?? ?? ????? ?? ????? ??? ????? ???, ????? ?? ?? ????? ?? ?????? ?? ????? ???..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources