coalasce unique values

Posted on 2012-08-18
Last Modified: 2012-08-18

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
Question by:johnkainn
    LVL 25

    Accepted Solution

    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


    Author Closing Comment

    Thank you.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now