Merge Multiple Rows Into Single Row

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Merge Multiple Rows Into Single Row

Author

Topic

gozst
Starting Member

4 Posts

Posted - 2010-02-24 : 12:57:31

Technician Table
TechnicianID FirstName LastName
RM1234 John Doe

User_Details Table
TechnicianID ServiceToolID
RM1234 1
RM1234 3

Territory_Technician_XRef Table
TechnicianID TerritoryID
RM1234 150
RM1234 155

ServiceTools Table
ServiceToolID ServiceTool
1 Tool One
3 Tool Three

Territory Table
TerritoryID ServiceProviderID
150 SP150
155 SP155

SELECT DISTINCT
Tech.TechnicianID,
Tech.FirstName,
Tech.LastName,
ST.ServiceTool,
Terr.ServiceProviderID
FROM Technician Tech
INNER JOIN User_Details UD
ON UD.TechnicianID = Tech.TechnicianID
INNER JOIN ServiceTools ST
ON UD.ServiceToolID = ST.ServiceToolID
INNER JOIN Territory_Technician_XRef TTXRef
ON TTXRef.TechnicianID = Tech.TechnicianID
INNER JOIN Territory Terr
ON Terr.TerritoryID = TTXRef.TerritoryID

What I end up with is:

TechnicianID FirstName LastName ServiceTool ServiceProviderID
RM1234 John Doe Tool One SP150
RM1234 John Doe Tool Three SP150
RM1234 John Doe Tool One SP155
RM1234 John Doe Tool Three SP155

What I would like to end up with is one row with comma separated values:

TechnicianID FirstName LastName ServiceTool ServiceProviderID
RM1234 John Doe Tool One, Tool Three SP150, SP155

I have seen the XML method, but am struggling. Any help is appreciated.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-24 : 16:01:52

See the blog for a good resource.

http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx

And an even better way that removes the last comma.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 16:13:49

Sample data

declare @t table
(technicianid varchar(10),firstname varchar(10), lastname varchar(30), servicetool varchar(50), serciveproviderid varchar(20))
insert @t
select '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'

Query

SELECT 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 s1
ORDER BY	s1.technicianid

Result

RM1234	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 DISTINCT
Tech.TechnicianID, 
Tech.FirstName, 
Tech.LastName, 
ST.ServiceTool,
Terr.ServiceProviderID
FROM Technician Tech
INNER JOIN User_Details UD
ON UD.TechnicianID = Tech.TechnicianID
INNER JOIN ServiceTools ST
ON UD.ServiceToolID = ST.ServiceToolID
INNER JOIN Territory_Technician_XRef TTXRef
ON TTXRef.TechnicianID = Tech.TechnicianID
INNER JOIN Territory Terr
ON 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 s1
ORDER 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.ServiceProviderID

FROM Technician Tech
ORDER BY ChangeDate DESC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 16:58:06

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources