February 25, 2021 at 6:18 pm
Hi,
In order to avoid redundant rows, I have to make the following changes to my SQL code :
For the same Quarters,Function, Product and Manager combination we want to update the [GM Scope] and [BL Scope] column as follows :
1) If Quarters,Function, Product and Manager are identical, then GM Scope should show all GM values of this combination separated by semicoln and space
2) If Quarters,Function, Product and Manager are identical, then BL Scope column should show combination of GM and BLs based on the following pattern GM : BL | GM : BL
For FMP 2 , Manager 1 , Q4 -20 AND PS combination, there are 2 BLs (MPS; WCE) for AZ and ALL for KZ.
AZ : MPS; WCE | KZ : ALL
Please help.
CREATE TABLE #ManagerProduct
(
[Quarters] nvarchar (10) ,
[Function] nvarchar (10) ,
[GM Scope] nvarchar (1000) ,
[GM] nvarchar (10) ,
[Product] nvarchar (1000) ,
[BL Scope] nvarchar (1000) ,
[BL] nvarchar (100) ,
[Manager] nvarchar (100) ,
)
INSERT INTO #ManagerProduct ([Quarters],[Function],[GM],[Product],[BL],[Manager])
SELECT 'Q4-20','TM','SC','FMP 1','WCE','Manager1' UNION
SELECT 'Q4-20','TM','AZ','FMP 1','WCE','Manager1' UNION
SELECT 'Q4-20','TM','KZ','FMP 1','WCF','Manager1' UNION
SELECT 'Q4-20','TM','SK','FMP 1','WCF','Manager1' UNION
SELECT 'Q4-20','PS','AZ','FMP 2','MPS; WCE','Manager1' UNION
SELECT 'Q4-20','PS','KZ','FMP 2','ALL','Manager1' UNION
SELECT 'Q4-20','FIN','AC','Job Probability','WCM; WCF','Manager3'
SELECT * FROM #ManagerProduct
-- Desired results
SELECT 'Q4-20'AS [Quarters],'FIN' AS [Function],'AC' AS [GM Scope],'Job Probability' AS [Product],'AC : WCM; WCF' AS [BL Scope] ,'Manager3' AS [Manager] UNION
SELECT 'Q4-20'AS [Quarters],'PS' AS [Function],'AZ; KZ' AS [GM Scope],'FMP 2' AS [Product], 'AZ : MPS; WCE | KZ : ALL' AS [BL Scope] ,'Manager1' AS [Manager] UNION
SELECT 'Q4-20'AS [Quarters],'TM' AS [Function],'SC; AZ; KZ; SK' AS [GM Scope],'FMP 1' AS [Product], 'SC : WCE | AZ : WCE | KZ : WCF | SK : WCF' AS [BL Scope] ,'Manager1' AS [Manager]
DROP TABLE #ManagerProduct
Thanks,
PSB
February 25, 2021 at 7:03 pm
This is pretty easy to do actually since you are on 2017!
Check this out:
SELECT [Quarters]
, [Function]
, [Product]
, [Manager]
, STRING_AGG([GM] ,'; ') AS [GM scope]
, STRING_AGG([GM] + ' : ' + [BL], ' | ') AS [BL Scope]
FROM [#ManagerProduct]
GROUP BY [Quarters], [Function], [Product], [Manager];
String_agg to combine the string, separated by the character specified, the group it by the other columns.
String_agg doesn't support windowing functions unfortunately, so you will need to group by all non-aggregate columns to make this work, but that works fine for the sample data provided.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 25, 2021 at 7:20 pm
Works like a charm.
Is there a way to sort the GM Scope and BL Scope columns in alphabetical order ?
-- Desired results
SELECT 'Q4-20'AS [Quarters],'FIN' AS [Function],'AC' AS [GM Scope],'Job Probability' AS [Product],'AC : WCF; WCM' AS [BL Scope] ,'Manager3' AS [Manager] UNION
SELECT 'Q4-20'AS [Quarters],'PS' AS [Function],'AZ; KZ' AS [GM Scope],'FMP 2' AS [Product], 'AZ : MPS; WCE | KZ : ALL' AS [BL Scope] ,'Manager1' AS [Manager] UNION
SELECT 'Q4-20'AS [Quarters],'TM' AS [Function],'SC; AZ; KZ; SK' AS [GM Scope],'FMP 1' AS [Product], 'AZ : WCE | KZ : WCF | SC : WCE | SK : WCF' AS [BL Scope] ,'Manager1' AS [Manager]
February 25, 2021 at 7:37 pm
Sorry about that. Did not realize the order was important.
But that is just as easy to handle:
SELECT [Quarters]
, [Function]
, [Product]
, [Manager]
, string_agg(GM ,'; ') WITHIN GROUP (ORDER BY GM) AS [GM scope]
, STRING_AGG([GM] + ' : ' + [BL], ' | ') WITHIN GROUP (ORDER BY GM, BL) AS [BL Scope]
FROM [#ManagerProduct]
GROUP BY [Quarters], [Function], [Product], [Manager]
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 25, 2021 at 7:41 pm
Thanks a lot. Works perfectly 🙂
February 25, 2021 at 8:04 pm
Happy to help. I am just glad you were on 2017 or newer as otherwise that STRING_AGG function wouldn't have worked and we'd have needed to get more creative.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 10, 2021 at 6:59 am
Usually, we use a SQL Plus (+) operator to perform SQL Server Concatenate operation with multiple fields together. We can specify space character as well in between these columns.
March 10, 2021 at 4:34 pm
One thing to note about Paige031's comment is that if you use the + operator on a VARCHAR and an INT column and the VARCHAR cannot be converted to INT, you will get errors. You will need to CAST/CONVERT your INT to VARCHAR.
Paige031's suggestion also only works on the "row" level for combining data, not column level like the string_agg function does.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply