Post reply

Convert column values into single row comma separator

  • February 17, 2020 at 5:45 am

    Go to Answer

    #3726312

    Team

    My requirement is to convert column values into single row comma separator along with single quotes. Pls help

    ServerName > Column Name

    Values in a row like below

    Server1

    server2

    server3

    server44

    I need a result like

    'Server1,server2,server3,server44'

    • This topic was modified 4 years, 9 months ago by  Rock.

  • February 17, 2020 at 6:14 am

    #3726315 

    SELECT ServerName + ', ' AS 'data()'
    FROM ServerList
    FOR XML PATH('');

  • February 17, 2020 at 7:59 am

    #3726326

    It doesnt work.

    The o/p should be like this

    'Server1,server2,server3'

    The current result is

    Server1, server2, server3

  • February 17, 2020 at 2:04 pm

    #3726428

    pietlinden wrote:

    SELECT ServerName + ', ' AS 'data()'
    FROM ServerList
    FOR XML PATH('');

    That's nice.  I've only ever seen string aggregation (prior to Sql Server 2016) done with the the 'for xml' and stuff() function combination.  The downside in the case is an extra comma at the end of the string which needs to be removed.

    Rock, the encompassing quotes need to be escaped because the tick mark is a reserved symbol in Sql Server.   A single tick mark when escaped is 4 ticks ''''

    /* xml data() column */
    ;with
    server_cte(ServerName) as (select 'Server1' union all select 'Server2'),
    servers_cte(ServersName) as (SELECT ServerName + ', ' AS 'data()' FROM server_cte FOR XML PATH(''))
    select
      concat('''', left(sc.ServersName, (len(sc.ServersName)-1)), '''')
    from
      servers_cte sc;

    /* xml + stuff function combo */
    ;with
    server_cte(ServerName) as (select 'Server1' union all select 'Server2')
    select 
      concat('''', stuff((select ', ' + ServerName from server_cte for xml path ('')), 1, 2, ''), '''');

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 17, 2020 at 3:08 pm

    #3726449

    Never mind.  Misread something.

    --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)

  • February 17, 2020 at 3:13 pm

    #3726451

    Removed

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 17, 2020 at 3:14 pm

    #3726452

    Ok cool

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 17, 2020 at 3:39 pm

    #3726469

    Steve Collins wrote:

    Oh wait Jeff you changed it.  Ok cool

    Yeah... my bad.  I've only got 2 cups of coffee under my belt and totally misread the code.

    --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)

  • February 18, 2020 at 4:43 am

    #3726635

    how do i set this to a local varabile in sql server

    I used this, it doesnt work.

    Declare @localvariable varchar(max)

    ;with

    server_cte(ServerName) as (select 'Server1' union all select 'Server2')

    select

    concat('''', stuff((select ', ' + ServerName from server_cte for xml path ('')), 1, 2, ''), '''');

    Select @localvariable = select count(*) from server_cte

     

    Error: Invalid Object Name server_cte MSG 208

  • February 18, 2020 at 4:55 am

    #3726638

    Select @localvariable = count(*) from server_cte

  • February 18, 2020 at 5:37 am

    #3726643

    Invalid object name 'server_cte'

  • February 18, 2020 at 5:39 am

    #3726644

    Wait wait wait...

    you can only reference a CTE immediately following the declaration. Why do you need a CTE for this at all?  What are you trying to accomplish?

  • February 18, 2020 at 9:00 am

    Answer

    #3726670

    Issue Resolved

    DECLARE @Hostnames VARCHAR(max)

    Select @Hostnames = concat('''', stuff((select ', ' + ServerName from #serverinput for xml path ('')), 1, 2, ''), '''');

    select @Hostnames

  • February 20, 2020 at 5:17 pm

    #3727430

    I realize you've already accepted an answer, but I'd like to offer an alternative solution that doesn't have anything to do with XML.

    /*the table where your data resides (I will use a table variable)*/
    DECLARE @temp AS TABLE(ServerName nvarchar(10) PRIMARY KEY);
    INSERT INTO @temp(ServerName)
    VALUES ('Server1'),
    ('server2'),
    ('server3'),
    ('server44');

    /*loop through the rows and create the comma-separated string*/
    DECLARE @str AS nvarchar(max) = NULL;
    SELECT @str = COALESCE(@str + ',' + ServerName, ServerName)
    FROM @temp;

    /*add the leading and trailing quotes*/
    SELECT '''' + @str + '''';

    • This reply was modified 4 years, 9 months ago by  jbowers. Reason: removed an apostrophe from the first comment, which was throwing off the formatting
    • This reply was modified 4 years, 9 months ago by  jbowers.

Viewing 14 posts - 1 through 13 (of 13 total)

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