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

  • 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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

    Cheers

    Andrew

     

  • Bingo! Works a treat.

    Many thanks

    Andrew

  • andrewjmdata wrote:

    Bingo! Works a treat.

    Many thanks

    Andrew

    Great! Thanks for posting back.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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