coalasce unique values

Posted on 2012-08-18
Medium Priority
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

lwadwell earned 2000 total points
ID: 38307808
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

ID: 38308020
Thank you.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

809 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