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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 20, 2021 at 2:32 pm
Thanks Phil, I'll have a look at this.
Cheers
Andrew
January 20, 2021 at 2:44 pm
Bingo! Works a treat.
Many thanks
Andrew
January 20, 2021 at 2:47 pm
Bingo! Works a treat.
Many thanks
Andrew
Great! Thanks for posting back.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply