Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select order and comma delimited list Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:39 AM
Points: 10, Visits: 34
Hi all,
Have an issue with a comma delimited list which is giving me a headache. Any suggestions would be appreciated.

Delimited list 1,3,2

I need to take each item from the list and match it to another table of names which in turn will be comma delimited.

mytable
ID Name
1 Fred
2 Joe
3 Bob

The result I'm looking for is: Fred,Bob, Joe

What's I've done is the following:
declare @path varchar(100)
select @path = '1,3,2'
stuff((select name from mytable where id in (select id FROM dbo.ufn_split(path) for xml path('')),1,1,'')

This works except that the "select ..where in " changes the order. Ie the names that are pulled from mytable are not in the same order as the id's in @path.

For clarity if you break down my code to:

select name from mytable where id in (select id FROM dbo.ufn_split(path))

it will give you a different order to this:

select id FROM dbo.ufn_split(path)


Any ideas?
thanks
Post #1419969
Posted Thursday, February 14, 2013 4:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
tut tut tut.... don't cross-post Kevin!

http://www.sqlservercentral.com/Forums/Topic1419967-8-1.aspx


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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1419970
Posted Thursday, February 14, 2013 4:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:39 AM
Points: 10, Visits: 34
Sorry. The other one is in the wrong forum. I went back to delete it but there is no "delete" option. Should be in this forum only.

thanks,
Post #1419971
Posted Thursday, February 14, 2013 4:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 2,380, Visits: 7,590
Sample data: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;

SELECT ID, Name
INTO #testEnvironment
FROM (VALUES(1, 'Fred'),(2, 'Joe'),(3, 'Bob'))a(ID, Name);

Query: -
DECLARE @Delimitedlist VARCHAR(8000) = '1,3,2';

SELECT STUFF((SELECT ','+a.Name
FROM #testEnvironment a
INNER JOIN (SELECT ItemNumber AS Ordering, Item
FROM dbo.DelimitedSplit8K(@Delimitedlist,',')
) b ON a.ID = b.Item
ORDER BY b.Ordering
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'');

Result: -
Fred,Bob,Joe


dbo.DelimitedSplit8K function can be found here --> http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Solution will only work if your original deliminated string is never more than 8000 characters.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1419987
Posted Thursday, February 14, 2013 7:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 6:39 AM
Points: 10, Visits: 34
Thanks for that. Worked perfectly.
Post #1420046
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse