Concatenate multiple records into one

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)

Concatenate multiple records into one

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-12-01 : 06:16:56

Hi,

I want to concatenate multiple records of a column in a single field like

employeeID city helpLineNumber
1,AAA,111&132&151
2,BBB,212&315
3,CCC,444
4,DDD,555

I have data in the below two tables:

CREATE TABLE #tempEmployeeHelpLineNumbers
(
employeeID VARCHAR(50),
city VARCHAR(50),
helpLineNumber VARCHAR(150)
)

INSERT #tempEmployeeHelpLineNumbers SELECT '1','AAA',NULL
INSERT #tempEmployeeHelpLineNumbers SELECT '2','BBB',NULL
INSERT #tempEmployeeHelpLineNumbers SELECT '3','CCC',NULL
INSERT #tempEmployeeHelpLineNumbers SELECT '4','DDD',NULL

CREATE TABLE #temphelpLineNumber
(
city VARCHAR(50),
helpLineNumber VARCHAR(50)
)

INSERT #temphelpLineNumber SELECT 'AAA','111'
INSERT #temphelpLineNumber SELECT 'AAA','132'
INSERT #temphelpLineNumber SELECT 'AAA','151'

INSERT #temphelpLineNumber SELECT 'BBB','212'
INSERT #temphelpLineNumber SELECT 'BBB','315'

INSERT #temphelpLineNumber SELECT 'CCC','444'

INSERT #temphelpLineNumber SELECT 'DDD','555'

I tried some thing like below:

UPDATE
a
SET a.helpLineNumber = ISNULL(a.helpLineNumber,'') +',' + b.helpLineNumber
FROM
#tempEmployeeHelpLineNumbers a
JOIN
#temphelpLineNumber b
ON
a.city = b.city

Not able to get the expected result.

Please advise..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 06:20:25

[code]
UPDATE a
SET a.HelpLineNumber = STUFF((SELECT '&' + helpLineNumber
FROM #temphelpLineNumber
WHERE city =a.city
ORDER BY helpLineNumber
FOR XML PATH('')),1,1,'')
FROM #tempEmployeeHelpLineNumbers a
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources