DECLARE @TableA AS table ( ID integer PRIMARY KEY, Sectio...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Database Administrators

Q&A for database professionals who wish to improve their database skills and learn from others in the community

DECLARE @TableA AS table
    (
        ID integer PRIMARY KEY,
        Section varchar(10) NOT NULL,
        AccountID char(2) NOT NULL
    );
    
    DECLARE @TableB AS table
    (
        AccountID char(2) PRIMARY KEY,
        Name varchar(20) NOT NULL
    );

    INSERT @TableA
        (ID, Section, AccountID)
    VALUES
        (1, 'shoes', 'A1'),
        (2, 'shoes', 'A2'),
        (3, 'shoes', 'A3'),
        (4, 'books', 'A1');
    
    INSERT @TableB
        (AccountID, Name)
    VALUES
        ('A1', 'AccountName1'),
        ('A2', 'AccountName2'),
        ('A3', 'AccountName3');

    DECLARE @x xml =
    (
        SELECT
            TA.Section,
            CA.Name
        FROM @TableA AS TA
        JOIN @TableB AS TB
            ON TB.AccountID = TA.AccountID
        CROSS APPLY
        (
            VALUES(',' + TB.Name)
        ) AS CA (Name)
        FOR XML AUTO, TYPE, ELEMENTS, ROOT ('Root')
    );

    SELECT
        Section = 
            N.n.value('(./Section/text())[1]', 'varchar(10)'),
        Names = 
            STUFF
            (
                -- Consecutive text nodes collapse
                N.n.query('./CA/Name/text()')
                .value('./text()[1]', 'varchar(8000)'), 
                1, 1, ''
            )
    -- Shred per section
    FROM @x.nodes('Root/TA') AS N (n);

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results