Concatenated Column - 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 2005 Forums

Transact-SQL (2005)

Concatenated Column

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2011-03-15 : 18:00:20

I have a table "EmployeeRoles" with columns "Employee_Id" and "Role".

There is no Primary Key as individual Employee_IDs can have more than one Role. I'm trying to create a select which will result in two columns... one for Employee_ID and one for a concatenated string of roles.

So, for example, if my EmployeeRoles table data contains:

EmpA, Role1
EmpA, Role2
EmpB, Role1
EmpC, Role3

I would like to have a select that returns

EmpA, Role1|Role2
EmpB, Role1
Empc, Role3

Now, I know how to do this if I want to concatenate records into a variable like:

DELCARE @roles nvarchar(max)

SET @roles = ''

SELECT @roles = @roles + er.Role + '|' FROM EmployeeRoles er

However, I need the concatenated string to be part of a select that returns the roles for each employee_id.

Can anyone help? Thanks in advance!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-15 : 18:13:30

Here is one way:

DECLARE @T TABLE (Employee_Id VARCHAR(10), Role VARCHAR(10))
INSERT @T VALUES 
('EmpA', 'Role1')
,('EmpA', 'Role2')
,('EmpB', 'Role1')
,('EmpC', 'Role3')


SELECT Employee_Id,
STUFF((SELECT '|' + Role FROM @T AS T1 WHERE T1.Employee_Id = T.Employee_Id ORDER BY T1.Role FOR XML PATH('')), 1, 1, '') AS Roles
FROM @T AS T
GROUP BY Employee_Id

akashenk
Posting Yak Master

111 Posts

Posted - 2011-03-15 : 18:27:36

Thanks! That did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources