| Author |
 Topic  |
|
|
marek
Starting Member
Slovakia
34 Posts |
 Posted - 06/15/2009 : 07:11:51
|
Pleas I need help.
TabCompany
id/Company
1/dell
2/canon
3/bros
4/hitachi
TabResponsibility
id/num
1/213
1/213
2/365
2/420
2/560
2/365
3/680
3/680
TabEmployees
num/surname
213/mark
365/alf
420/bob
560/cruise
680/smith
results:
id/company/surname
1/dell/mark
2/canon/alf,bob,cruise
3/bros/smith
4/hitachi
TabCompany.id=TabResponsibility.id and TabResponsibility.num=TabEmployees.num
It is possible to join this tables?
thanks for syntax
Thanks very much. |
Edited by - marek on 06/15/2009 12:51:32
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 06/15/2009 : 07:51:11
|
This looks exactly the same as your previous post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127546
What have you tried so far?
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
marek
Starting Member
Slovakia
34 Posts |
Posted - 06/15/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 06/15/2009 : 09:06:51
|
try this
SELECT c.id,c.Company,e.num,e.surname into #temp
FROM TabCompany c
INNER JOIN TabResponsibility r ON r.id = c.id
INNER JOIN TabEmployees e ON e.num = r.num
SELECT id,company,STUFF((SELECT ',' + surname FROM #temp WHERE num = t.num FOR XML PATH('')),1,1,'') AS surname
FROM #temp t
|
|
|
|
marek
Starting Member
Slovakia
34 Posts |
Posted - 06/15/2009 : 12:51:05
|
it is not good, result is:
1/dell/mark
1/dell/mark
2/canon/alf
2/canon/alf
2/canon/bob
2/canon/cruise
3/bros/smith
3/bros/smith
result must by this format:
id/company/surname
1/dell/mark
2/canon/alf,bob,cruise
3/bros/smith
4/hitachi
pleas if is possible without temp, thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/15/2009 : 14:00:42
|
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
|
|
|
|
marek
Starting Member
Slovakia
34 Posts |
Posted - 06/15/2009 : 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
Slovenia
59 Posts |
Posted - 11/21/2012 : 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 commands
big thanks |
Edited by - Gekko on 11/21/2012 03:14:55 |
|
|
| |
Topic |
|
join 3 tables
