SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Distinct Coalesce from Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wilshaw
Starting Member

United Kingdom
10 Posts

Posted - 05/01/2013 :  17:18:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/01/2013 :  18:33:20  Show Profile  Reply with Quote
You need to give an alias to the subquery.
FROM (SELECT DISTINCT airport FROM Administrators
WHERE companyID = @CompanyID) AS S
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/01/2013 :  18:53:21  Show Profile  Reply with Quote
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

Edited by - UnemployedInOz on 05/01/2013 19:14:05
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/02/2013 :  01:17:27  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wilshaw
Starting Member

United Kingdom
10 Posts

Posted - 05/02/2013 :  04:32:20  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000