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 |
|
gozst
Starting Member
4 Posts |
Posted - 2010-02-24 : 12:57:31
|
| Technician TableTechnicianID FirstName LastNameRM1234 John DoeUser_Details TableTechnicianID ServiceToolIDRM1234 1RM1234 3Territory_Technician_XRef TableTechnicianID TerritoryIDRM1234 150RM1234 155ServiceTools TableServiceToolID ServiceTool1 Tool One3 Tool ThreeTerritory TableTerritoryID ServiceProviderID150 SP150155 SP155SELECT DISTINCT Tech.TechnicianID, Tech.FirstName, Tech.LastName, ST.ServiceTool, Terr.ServiceProviderIDFROM Technician TechINNER JOIN User_Details UDON UD.TechnicianID = Tech.TechnicianIDINNER JOIN ServiceTools STON UD.ServiceToolID = ST.ServiceToolIDINNER JOIN Territory_Technician_XRef TTXRefON TTXRef.TechnicianID = Tech.TechnicianIDINNER JOIN Territory TerrON Terr.TerritoryID = TTXRef.TerritoryIDWhat I end up with is:TechnicianID FirstName LastName ServiceTool ServiceProviderIDRM1234 John Doe Tool One SP150RM1234 John Doe Tool Three SP150RM1234 John Doe Tool One SP155RM1234 John Doe Tool Three SP155What I would like to end up with is one row with comma separated values:TechnicianID FirstName LastName ServiceTool ServiceProviderIDRM1234 John Doe Tool One, Tool Three SP150, SP155I have seen the XML method, but am struggling. Any help is appreciated. |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-24 : 16:13:49
|
Sample datadeclare @t table(technicianid varchar(10),firstname varchar(10), lastname varchar(30), servicetool varchar(50), serciveproviderid varchar(20))insert @tselect 'RM1234', 'John', 'Doe', 'Tool One', 'SP150'union all select 'RM1234', 'John' ,'Doe' ,'Tool Three', 'SP150'union all select 'RM1234', 'John', 'Doe' ,'Tool One', 'SP155'union all select 'RM1234', 'John', 'Doe' ,'Tool Three', 'SP155' QuerySELECT DISTINCT s1.technicianid,s1.firstname,s1.lastname,STUFF((SELECT DISTINCT ',' + s2.servicetool FROM @t AS s2 WHERE s2.technicianid = s1.technicianid ORDER BY ',' + s2.servicetool FOR XML PATH('')), 1, 1, '') AS [servicetool],STUFF((SELECT DISTINCT ',' + s2.serciveproviderid FROM @t AS s2 WHERE s2.technicianid = s1.technicianid ORDER BY ',' + s2.serciveproviderid FOR XML PATH('')), 1, 1, '') AS [serciveproviderid]FROM @t AS s1ORDER BY s1.technicianidResultRM1234 John Doe Tool One,Tool Three SP150,SP155 |
 |
|
|
gozst
Starting Member
4 Posts |
Posted - 2010-02-24 : 16:15:36
|
| I would like to use the XML approach to do this, but I am struggling to get both ServiceTool and ServiceProvider to merge in the same query. And samples that could be posted would be very helpful. |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-24 : 16:17:21
|
quote: Originally posted by gozst I would like to use the XML approach to do this, but I am struggling to get both ServiceTool and ServiceProvider to merge in the same query. And samples that could be posted would be very helpful.
I just did that...no? |
 |
|
|
gozst
Starting Member
4 Posts |
Posted - 2010-02-24 : 16:21:17
|
| Thank you for the post, but the issue I am having is mapping the tables together and completing the merge. The ServiceTools tables does not contain a TechnicianID, so the s2 reference will not work. |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-24 : 16:25:56
|
Use a temp table or CTE to popultae your result and operate on that..like;with test_cte (technicianid,firstname,lastname,servicetool,serciveproviderid)as(SELECT DISTINCTTech.TechnicianID, Tech.FirstName, Tech.LastName, ST.ServiceTool,Terr.ServiceProviderIDFROM Technician TechINNER JOIN User_Details UDON UD.TechnicianID = Tech.TechnicianIDINNER JOIN ServiceTools STON UD.ServiceToolID = ST.ServiceToolIDINNER JOIN Territory_Technician_XRef TTXRefON TTXRef.TechnicianID = Tech.TechnicianIDINNER JOIN Territory TerrON Terr.TerritoryID = TTXRef.TerritoryID)SELECT DISTINCT s1.technicianid,s1.firstname,s1.lastname,STUFF((SELECT DISTINCT ',' + s2.servicetool FROM test_cte AS s2 WHERE s2.technicianid = s1.technicianid ORDER BY ',' + s2.servicetool FOR XML PATH('')), 1, 1, '') AS [servicetool],STUFF((SELECT DISTINCT ',' + s2.serciveproviderid FROM test_cte AS s2 WHERE s2.technicianid = s1.technicianid ORDER BY ',' + s2.serciveproviderid FOR XML PATH('')), 1, 1, '') AS [serciveproviderid]FROM test_cte AS s1ORDER BY s1.technicianid |
 |
|
|
gozst
Starting Member
4 Posts |
Posted - 2010-02-24 : 16:50:53
|
| Got it. Thanks!SELECT DISTINCT Tech.TechnicianID, Tech.FirstName, Tech.LastName, (SELECT DISTINCT STUFF( ( SELECT ', ' + ServiceProviderID FROM Territory Terr, Technician Tech2, Territory_Technician_XRef TTXRef WHERE Tech2.TechnicianID = Tech.TechnicianID AND TTXRef.TechnicianID = Tech.TechnicianID AND Terr.TerritoryID = TTXRef.TerritoryID FOR XML PATH('') ),1,1,'')) AS [ServiceProdiverIDs], (SELECT DISTINCT STUFF( ( SELECT ', ' + ServiceTool FROM ServiceTools ST, Technician Tech2, User_Details UD WHERE Tech2.TechnicianID = Tech.TechnicianID AND UD.TechnicianID = Tech.TechnicianID AND UD.ServiceToolID = ST.ServiceToolID FOR XML PATH('') ),1,1,'')) AS [ServiceTools]-- ST.ServiceTool,-- Terr.ServiceProviderIDFROM Technician TechORDER BY ChangeDate DESC |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-24 : 16:58:06
|
| Np. You're welcome. |
 |
|
|
|
|
|
|
|