Post reply

select order and comma delimited list

  • February 14, 2013 at 4:28 am

    #289291

    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

  • February 14, 2013 at 4:29 am

    #1587837

    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[/url]

    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

  • February 14, 2013 at 4:31 am

    #1587838

    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,

  • February 14, 2013 at 4:58 am

    #1587851

    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/%5B/url%5D.

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

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    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/

  • February 14, 2013 at 7:09 am

    #1587906

    Thanks for that. Worked perfectly.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply