Author |
Topic |
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2013-01-31 : 13:10:09
|
i have T1 (table1)--Column 0|Column 1Iff9 43410Iff9 43490S0013S 9045561 S90014A 8q2540 S91214D Vqq9 ---I have T2 (Table2)----Field1|Field2S91214D S91214DS91214D Iff9 S0013S nullnull Iff9 Iff9 Iff9 --- I have this but it doesnt show what I need to:select Field1,Field2,case when Field1=column0 then column1 Replacement_Field1,case when Field2=column0 then column1 Replacement_Field2 from T1, T2where Field1=column0 or Field2=column0 or (Field1=column0 and Field2=column0) Basically (Field1 and Field2) have the same values as Column0 in table1Desired result is:Field1 |Field2 |Replacement_Field1|Replacement_Field2S91214D S91214D Vqq9 Vqq9S91214D Iff9 Vqq9 43410,43490S0013S null 9045561 no matchnull Iff9 no match 43410,43490Iff9 Iff9 43410,43490 43410,43490 Thanks for the help. --------------------------Joins are what RDBMS's do for a living |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 13:26:40
|
[code]SELECT t2.Field1,t2.Field2,STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field1 FOR XML PATH()),1,1,'') AS ReplacementField1,STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field2 FOR XML PATH()),1,1,'') AS ReplacementField2FROM Table2 t2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
MatiTuk
Starting Member
3 Posts |
Posted - 2013-01-31 : 13:43:36
|
Try something like this.Select T2.Field1, T2.Field2, CASE when (T2.Field1 = T1.Column0) Then ISNULL(T1.Column1,'') ELSE '' as Replacement_Field1, CASE when (T2.Field2 = T1.Column0) Then ISNULL(T1.Column1,'') ELSE '' as Replacement_Field2from Table1 T1 inner join Table2 T2 on T1.Field1 = T2.Column0where T1.Field1 = T1.Column0or T1.Field2 = T1.Column0 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 13:55:01
|
quote: Originally posted by visakh16
SELECT t2.Field1,t2.Field2,STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field1 FOR XML PATH('')),1,1,'') AS ReplacementField1,STUFF((SELECT ',' + Column1 FROM table1 WHERE Column0 = t2.Field2 FOR XML PATH('')),1,1,'') AS ReplacementField2FROM Table2 t2 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Fixing a typo. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 13:56:17
|
Thanks James------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2013-01-31 : 14:57:03
|
Thanks! Visakh :)There something wrong with the STUFF() function.It gives me an error of parenthesis.!!Any idea why please?--------------------------Joins are what RDBMS's do for a living |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-01-31 : 15:44:26
|
did you use Jame's corrected-for-typo version?Be One with the OptimizerTG |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2013-01-31 : 20:22:48
|
Its all set guys,Thank you ALL for the support.--------------------------Joins are what RDBMS's do for a living |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-01 : 02:37:18
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|