Post reply

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

  • January 20, 2021 at 10:10 am

    Go to Answer

    #3835427

    Hi, I have a table of sites where the there are multiple site names with different site managers. See below....

    What I need is to transform the data on the left to the data on the right. The important column on the right being "All Managers". The sales and items is simple group by which I know how to do.

    There could be up to 20 managers for a site. I want a single record for the site with all managers shown in the "All Managers" field.

    I could add row_number (using "row_number over partition by...") and then select based on row_number 1...n and then join all queries back together but want to know if there is a cleaner method.

    Any help appreciated!

    Thanks

    Andrew

  • 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;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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