Post reply

Help with Dynamic SQL

  • June 3, 2019 at 7:39 pm

    #3649895

    I'm running a query that returns a list separated by commas in an nvarchar field.

    Ex - 1595, 1598

    1598

    1567, 1456, 1234

     

    I need to return these as the following:

    '1595', '1598'

    '1598'

    '1567', '1456', '1234'

     

    Does anyone have a function that would essentially put a tick mark at the beginning, end and wrap a comma with tick marks in an nvarchar field?

  • June 3, 2019 at 7:54 pm

    #3649897

    We're missing the SQL, so I can only show you an example. I suspect that you also want to ensure that any ' in the value are correctly escaped, so what you might do is something like:

    WITH VTE AS(
        SELECT *
        FROM (VALUES (1,1595),
                     (1,1598),
                     (2,1598),
                     (3,1567),
                     (3,1456),
                     (3,1234)) V(ID,Num))
    SELECT STUFF((SELECT ',' + QUOTENAME(sq.Num,'''')
                  FROM VTE sq
                  WHERE sq.ID = V.ID
                  FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'')
    FROM VTE V
    GROUP BY V.ID;

    Note, that this won't work as you expect if you have values longer than 128 characters, but considering your sample data I assume that isn't the case.

    • This reply was modified 5 years, 5 months ago by  Thom A. Reason: Missed out a qualifer on a column, whoops

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 3, 2019 at 7:57 pm

    #3649898

    Thank you!!

Viewing 3 posts - 1 through 2 (of 2 total)

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