concat names into one record - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

concat names into one record

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-08 : 15:26:48

I have to combine the "e.LastName, e.FirstName, e.MiddleInitial
" from the tblTrkRecordAnalyst table into one field and add it to the Id field from the tblTrkRecord table. There could be multiple tblTrkRecordAnalyst for eacch record. Also I can not have a comma at the end of Name

The query below gives me:
Id LastName FirstName MiddleInitial
3 Jones Bob L
3 Smith Jim C

I need
Id Name
3 Jones Bob L, Smith Jim C

SELECT tr.Id, e.LastName, e.FirstName, e.MiddleInitial
FROM dbo.tblTrkRecord AS tr INNER JOIN
dbo.tblTrkRecordAnalyst AS tra ON tr.Id = tra.RecordId INNER JOIN
dbo.tblEmployee AS e ON tra.EmployeeId = e.Id
WHERE (tr.IdNum = 205861)

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-08 : 15:53:40

[code];WITH cte AS
(
SELECT tr.Id ,
e.LastName ,
e.FirstName ,
e.MiddleInitial
FROM dbo.tblTrkRecord AS tr
INNER JOIN dbo.tblTrkRecordAnalyst AS tra ON tr.Id = tra.RecordId
INNER JOIN dbo.tblEmployee AS e ON tra.EmployeeId = e.Id
WHERE ( tr.IdNum = 205861 )
)
SELECT
a.id,
STUFF(b.Namelist,1,1,'') AS Namelist
FROM
(SELECT DISTINCT Id FROM cte) a
CROSS APPLY
(
SELECT ','+
COALESCE(Lastname + ' ','') +
COALESCE(FirstName + ' ','') +
COALESCE(MiddleInitial,'')
FROM cte b
WHERE b.id = a.Id
) b(Namelist);[/code]The query within the CTE is your original query.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-10-08 : 15:58:28

That will not work It needs to be one record
Multiple name per record

Dave
Helixpoint Web Development
http://www.helixpoint.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-08 : 17:44:22

Sorry forgot the FOR XML PATH construct - see changes in red.

;WITH cte AS 
(
	SELECT  tr.Id ,
			e.LastName ,
			e.FirstName ,
			e.MiddleInitial
	FROM    dbo.tblTrkRecord AS tr
			INNER JOIN dbo.tblTrkRecordAnalyst AS tra ON tr.Id = tra.RecordId
			INNER JOIN dbo.tblEmployee AS e ON tra.EmployeeId = e.Id
	WHERE   ( tr.IdNum = 205861 )
)
SELECT
	a.id,
	STUFF(b.Namelist,1,1,'') AS Namelist
FROM
	(SELECT DISTINCT Id FROM cte) a
	CROSS APPLY 
	(
		SELECT ','+
		COALESCE(Lastname + ' ','') +
		COALESCE(FirstName + ' ','') +		
		COALESCE(MiddleInitial,'')
		FROM cte b
		WHERE b.id = a.Id
		FOR XML PATH('')
	) b(Namelist);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources