Distinct Coalesce from Function

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 2012 Forums

Transact-SQL (2012)

Distinct Coalesce from Function

Author

Topic

wilshaw
Starting Member

10 Posts

Posted - 2013-05-01 : 17:18:27

Hi, I'm trying to get DISTINCT values (airport) from a scalar-valued function:-

DECLARE @AirportList NVARCHAR(MAX)
SELECT @AirportList =
COALESCE(@AirportList + ',', '') + CONVERT(NVARCHAR(50), airport)
FROM Administrators
WHERE companyID = @CompanyID

RETURN @AirportList

---------------------------------------------------------

I've tried this as the FROM statement:-

FROM (SELECT DISTINCT airport FROM Administrators
WHERE companyID = @CompanyID)

RETURN @AirportList

But that is throwing an error at the RETURN statement.

Any help would be appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-01 : 18:33:20

You need to give an alias to the subquery.

FROM (SELECT DISTINCT airport FROM Administrators
WHERE companyID = @CompanyID) AS S

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-01 : 18:53:21

Declare @Airport varchar(max)
select @Airport = (select ',' + CONVERT(NVARCHAR(50), airport)
FROM (SELECT distinct airport from Administrators
WHERE companyID = @CompanyID) z
for xml path(''))

select @Airport = substring(@airport,2,len(@airport) - 1)
select @Airport

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:17:27

[code]
Declare @Airport varchar(max)
select @Airport = STUFF((SELECT distinct ',' + CONVERT(NVARCHAR(50), airport) from Administrators
WHERE companyID = @CompanyID for xml path('')),1,1,'')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wilshaw
Starting Member

10 Posts

Posted - 2013-05-02 : 04:32:20

quote:
Originally posted by James K

You need to give an alias to the subquery.
FROM (SELECT DISTINCT airport FROM Administrators
WHERE companyID = @CompanyID) AS S

Thats got it thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources