Post reply

Multiple rows of same site with different managers for each - how to pivot

  • January 20, 2021 at 12:52 pm

    Answer

    #3835475

    STRING_AGG() is your friend here, but the fact that it does not support DISTINCT (eg, to avoid John/John/David) complicates things somewhat. Here is one solution.

    DROP TABLE IF EXISTS #Sales;

    CREATE TABLE #Sales (Site VARCHAR(29), Manager VARCHAR(100), Sales INT, Items INT);

    INSERT #Sales (Site, Manager, Sales, Items)
    VALUES
    ('Leeds', 'John', 1000, 28)
    ,('Leeds', 'David', 2000, 32)
    ,('Leeds', 'John', 1599, 41)
    ,('Manchester', 'Andrew', 2000, 19);

    SELECT
        s.Site
    ,   AllManagers = calcs.AllManagers
    ,   Sales = SUM(s.Sales)
    ,   Items = SUM(s.Items)
    FROM #Sales s
        CROSS APPLY
    (
        SELECT AllManagers = STRING_AGG(a.Manager, '/') WITHIN GROUP(ORDER BY a.Manager)
        FROM
        (SELECT DISTINCT s1.Site, s1.Manager FROM #Sales s1) a
        WHERE a.Site = s.Site
    ) calcs
    GROUP BY s.Site
    ,        calcs.AllManagers;

  • January 20, 2021 at 2:32 pm

    #3835512

    Thanks Phil, I'll have a look at this.

    Cheers

    Andrew

     

  • January 20, 2021 at 2:44 pm

    #3835515

    Bingo! Works a treat.

    Many thanks

    Andrew

  • January 20, 2021 at 2:47 pm

    #3835516

    andrewjmdata wrote:

    Bingo! Works a treat.

    Many thanks

    Andrew

    Great! Thanks for posting back.

Viewing 4 posts - 1 through 5 (of 5 total)

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