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.
| Author |
Topic |
|
sahu74
Posting Yak Master
100 Posts |
 Posted - 2005-05-25 : 10:34:47
|
| [code]I have two tables TblA and TblBColumns in TblA are (UID, UAbbr, UName,TID)Examples of some rows of TblA are UID UAbbr UName TID --- ----- ------------- ----- 1 C1 ClassA 1 2 C2 ClassB 1 3 C3 ClassC 1 5 C4 ClassE 1 6 C5 Grade4 1 10 C6 Grade5 1 Columns in TblB are (C1, C2,C3,C4,C5, C6, Yr, CID, DID)Examples of some rows of TblB are:C1 C2 C3 C4 C5 C6 Yr CID DID-----------------------------------------------------------------------20024 2517 998 619 24158 1077 1998 1 020227 2477 947 598 24249 1095 1999 1 1120420 2534 1121 642 24717 1065 1996 6 6220518 2413 976 651 24558 1090 2000 5 5420806 2532 1048 636 25022 1094 1997 5 54[/code]for a given CID, DID and Yr, I want to tie the column values of column C1, C2, C3,... from TblB with the UName (ClassA, ClassB, ClassC) in TblA through the UAbbr in TblA. I would like to have the final results in the following format (For CID= 1 and DID = 11 and Yr = 1999):[code] UID UAbbr UName TID Value --- ----- ------------- ----- ------ 1 C1 ClassA 1 20227 2 C2 ClassB 1 2477 3 C3 ClassC 1 947 5 C4 ClassE 1 598 6 C5 Grade4 1 24249 10 C6 Grade5 1 1095[/code]Could anyone suggest a way to get to this? Any help will be appreciated.Thank you.PKS |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-05-25 : 12:19:45
|
| Will this work?SELECT TblA.UID, TblA.UAbbr, TblA.UName, TblA.TID, CASE TblA.UAbbr WHEN 'C1' THEN B.C1 WHEN 'C2' THEN B.C2 WHEN 'C3' THEN B.C3 WHEN 'C4' THEN B.C4 WHEN 'C5' THEN B.C5 WHEN 'C6' THEN B.C6 END FROM TblA, (SELECT * FROM TblB WHERE CID = 1 AND DID = 11 AND Yr = 1999) AS B |
 |
|
|
sahu74
Posting Yak Master
100 Posts |
Posted - 2005-05-25 : 13:10:17
|
| That works great. Thank you.PKS. |
|
|
|
|
|
|
|
|
How to join this data from two tables???