Author |
Topic |
marek
Starting Member
34 Posts |
Posted - 2009-06-15 : 07:11:51
|
Pleas I need help. TabCompany id/Company1/dell2/canon3/bros4/hitachiTabResponsibilityid/num1/2131/2132/3652/4202/5602/3653/6803/680 TabEmployeesnum/surname213/mark365/alf420/bob560/cruise680/smithresults:id/company/surname1/dell/mark2/canon/alf,bob,cruise3/bros/smith4/hitachiTabCompany.id=TabResponsibility.id and TabResponsibility.num=TabEmployees.numIt is possible to join this tables?thanks for syntaxThanks very much. |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-15 : 07:51:11
|
This looks exactly the same as your previous post:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127546What have you tried so far?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
marek
Starting Member
34 Posts |
Posted - 2009-06-15 : 08:54:49
|
It is not same. There are 3 tables, no 2 tables. It before is one report(resolved, thanks visakh16), and this problem is second report. I have one table called "betweentab. TabResponsibility" and I dont know how I give into syntax.Could you please help me with this problem? I cant solve it alone. Thanks |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-06-15 : 09:06:51
|
try thisSELECT c.id,c.Company,e.num,e.surname into #tempFROM TabCompany cINNER JOIN TabResponsibility r ON r.id = c.idINNER JOIN TabEmployees e ON e.num = r.numSELECT id,company,STUFF((SELECT ',' + surname FROM #temp WHERE num = t.num FOR XML PATH('')),1,1,'') AS surnameFROM #temp t |
|
|
marek
Starting Member
34 Posts |
Posted - 2009-06-15 : 12:51:05
|
it is not good, result is:1/dell/mark1/dell/mark2/canon/alf2/canon/alf2/canon/bob2/canon/cruise3/bros/smith3/bros/smithresult must by this format:id/company/surname1/dell/mark2/canon/alf,bob,cruise3/bros/smith4/hitachipleas if is possible without temp, thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-15 : 14:00:42
|
[code]SELECT c.id,c.Company,STUFF((SELECT DISTINCT ','+e.surname FROM TabResponsibility r INNER JOIN TabEmployees e ON e.num=r.num WHERE r.id=c.id FOR XML PATH('')),1,1,'')FROM TabCompany c[/code] |
|
|
marek
Starting Member
34 Posts |
Posted - 2009-06-15 : 16:41:51
|
visakh16 realy realy big thanks...Thanks also "bklr"This is exactly what I want to do.good look |
|
|
Gekko
Yak Posting Veteran
63 Posts |
Posted - 2012-11-21 : 03:04:10
|
quote: Originally posted by visakh16
SELECT c.id,c.Company,STUFF((SELECT DISTINCT ','+e.surname FROM TabResponsibility r INNER JOIN TabEmployees e ON e.num=r.num WHERE r.id=c.id FOR XML PATH('')),1,1,'')FROM TabCompany c
Mr. visakh16 What if I want insert cast and convert e.surname on varchar?quote: SELECT c.id,c.Company, salesman= CAST STUFF((SELECT DISTINCT ','+CONVERT(varchar, e.surname) FROM TabResponsibility r INNER JOIN TabEmployees e ON e.num=r.num WHERE r.id=c.id FOR XML PATH('')),1,1,'') as varchar (25)FROM TabCompany c
quote has error. probably a mistake "(" or sequence of commandsbig thanks |
|
|
|