coalasce unique values

Hello,

I am trying to use coalasce to create a string. There are duplicate values of Name in the table.
If I set Distinct in front then I only get one item. How can I get only unique values in @DepartmentName?

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'  
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames
johnkainnAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
COALESCE returns the first 'non-null' value from the enclosed list.

Are you trying to create a list of values from a set of rows?  If so, look at this example
with HumanResources as (
select 'HR' as Department union all
select 'HR' as Department union all
select 'IT' as Department union all
select 'Payroll' as Department union all
select 'Marketing' as Department
)
select stuff((SELECT DISTINCT ', ' + Department FROM HumanResources FOR XML PATH ('')), 1, 2, '') as Departments

Open in new window


Hence I think you want:
select @DepartmentName = stuff((SELECT DISTINCT ';' + Department 
                FROM HumanResources 
                WHERE (GroupName = 'Executive General and Administration')
                FOR XML PATH ('')), 1, 1, '')

Open in new window

0
 
johnkainnAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.