locked
How to get row values as String in result set RRS feed

  • Question

  • User1279383592 posted

    Hello Everyone,

    I'm trying to achieve the following. 

    Table A - 

    ID     CountryCode

    1           US

    2           UK

    Table B - 

    ID        Code       State

    1            US           Alabama

    2            US           Alaska

    3           US            Arizona

    I want the result set (for US Code) in the following format (i.e. string in one cell) - 

    Alabama, Alaska, Arizona 

    Please suggest how to achieve the same.

    Thanks in Adavnce!!!

    Monday, October 31, 2016 7:32 PM

Answers

  • User1279383592 posted

    Alright sorted. I forgot additional open parenthesis symbol post stuff keyword i.e.

    SELECT STUFF
    ((
    SELECT ',' + Cast(State as varchar(50)) from tabB
    where tabB.code = tabA.CountryCode
    FOR XML PATH('')
    )
    ,1,1,'') AS RESULTSET
    FROM TabA
    where TabA.id=1

    It's working :). I saw stuff command in other blogs too but never made it to usage. Finally got a link that helped. Anyways thanks code movement for suggestion (unfortunately tables which one to refer is not clear in reply).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 1, 2016 5:57 AM

All replies

  • User-691209617 posted

    Hi,

    Please see below mentioned query

    SELECT STUFF((
            select ','+ State 
            from Code
            FOR XML PATH('')
            )
            ,1,1,'') AS US_Codes

    Hope it helps

    Monday, October 31, 2016 7:46 PM
  • User1279383592 posted

    Well this is what I'm doing.

    SELECT STUFF

    (
    SELECT ',' + State from tabB
    where tabB.code = tabA.CountryCode
    FOR XML PATH('')
    )
    ,1,1,'') AS RESULTSET
    FROM TabA
    where TabA.id=1

    Note - Tab A has 2 columns, Tab B has 3 columns. It isn't working.

    Tuesday, November 1, 2016 5:52 AM
  • User1279383592 posted

    Alright sorted. I forgot additional open parenthesis symbol post stuff keyword i.e.

    SELECT STUFF
    ((
    SELECT ',' + Cast(State as varchar(50)) from tabB
    where tabB.code = tabA.CountryCode
    FOR XML PATH('')
    )
    ,1,1,'') AS RESULTSET
    FROM TabA
    where TabA.id=1

    It's working :). I saw stuff command in other blogs too but never made it to usage. Finally got a link that helped. Anyways thanks code movement for suggestion (unfortunately tables which one to refer is not clear in reply).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 1, 2016 5:57 AM