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.
| Author |
Topic |
|
gwjones12
Starting Member
9 Posts |
 Posted - 2011-04-25 : 14:49:12
|
| This function works great, it returns a list of all Organizations.OrgName rows related to the passed ID in a single line. What I need is for the OrgName list to be sorted. How?ThanksALTER FUNCTION [dbo].[GetOrgNamesByListID] (@IDs varchar(500)) RETURNS varchar(max) AS BEGIN declare @output varchar(max) select @output = COALESCE(@output + ', ', '') + OrgName from dbo.Organizations INNER JOIN (select ltrim(rtrim(mynode.value('.[1]', 'nvarchar(12)'))) as ID from (select cast('<v>'+ replace(@IDs,',','</v><v>') + '</v>' as xml) doc) xx cross apply doc.nodes('/v') (mynode) ) as tbl ON OrganizationID = tbl.IDreturn @output END |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
gwjones12
Starting Member
9 Posts |
Posted - 2011-04-26 : 11:12:01
|
quote:
Originally posted by tkizer
Did you try adding an ORDER BY for OrgName?Tara Kizer
Humm, I was sure that it didn't work when I tried it before, but it does now!declare @output varchar(max) select @output = COALESCE(@output + ', ', '') + OrgName from dbo.Organizations INNER JOIN (select ltrim(rtrim(mynode.value('.[1]', 'nvarchar(12)'))) as ID from (select cast('<v>'+ replace(@IDs,',','</v><v>') + '</v>' as xml) doc) xx cross apply doc.nodes('/v') (mynode) ) as tbl ON OrganizationID = tbl.ID order by orgname |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Possible to Sort this?
Tara Kizer