CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

  • twin.devil

    SSC-Insane

    Points: 22208

    Glad to help.

  • repalaa

    Grasshopper

    Points: 10

    ColdCoffee - Friday, March 2, 2012 12:14 PM

    Similar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.value FROM [test12] InrTab WHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitle ORDER BY InrTab.value FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM [test12] OutTabGROUP BY OutTab.title , OutTab.[subtitle] ;

    the examples that are provided are nested select statements, Is there a way that can be moved to a join ? and bring the column from that joined table?

  • drew.allen

    SSC Guru

    Points: 76587

    repalaa - Thursday, January 24, 2019 1:29 PM

    ColdCoffee - Friday, March 2, 2012 12:14 PM

    Similar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.value FROM [test12] InrTab WHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitle ORDER BY InrTab.value FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM [test12] OutTabGROUP BY OutTab.title , OutTab.[subtitle] ;

    the examples that are provided are nested select statements, Is there a way that can be moved to a join ? and bring the column from that joined table?

    If you want to replace the subquery with a join, you'll need to use STRING_AGG() in place of the XML concatenation method.  STRING_AGG() is available starting in SQL 2017.

    Drew

    PS: You do realize that this thread is almost six years old.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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