Post reply

SQL Function to Split Comma Separated Values and Insert into Table

  • December 3, 2010 at 5:57 am

    #1258334

    TurnerC,

    Someone will probably point out a much better way of doing this but getting a single column of multiple records into a comma-separated string can be done thusly:

    SELECT STUFF((SELECT ',' + CAST(TableName.NumBeds AS varchar) FROM TableName FOR XML PATH('')),1, 2, '') AS CSVColumn

    You have to CAST any output fields as strings (as long as they aren't already, I'm presuming your Number of Beds field would be an integer) for it to work.

  • December 3, 2010 at 6:08 am

    #1258339

    Thank you for your reply. I will try it out.

    Would you happen to know if there are performance issues with using Coalesce. I read an article on how to creat a csv file using Coalesce. I am just unsure about the performance side.

    Thank you

  • December 3, 2010 at 6:40 am

    #1258362

    Would the COALESCE be this sort of query?:

    DECLARE @EmployeeList varchar(100)

    SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(Emp_UniqueID AS varchar(5))

    FROM SalesCallsEmployees

    WHERE SalCal_UniqueID = 1

    SELECT @EmployeeList

    In my experience COALESCE can be a bit of a performance hit. Certainly the above on one of my database columns (varchar(255)) with 22,000+ records the above is about 3-4 times slower than the STUFF...XML method.

  • December 3, 2010 at 8:28 pm

    #1258707

    WayneS (12/1/2010)

    chris-860960 (11/30/2010)

    Just switched to WayneS' non-RBAR function. Now that's FAST!

    It's not "my" function - it comes from Jeff Moden. It's just what is in use around here, with little tweaks here and there to make it as fast as it possible can be.

    Thanks for the kudo but it's not my original idea, either. It's an assembly of good practice from many authors with many lessons learned. Many others have have put together similar functions.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 16 through 18 (of 18 total)

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