Having a complex query result and searching for a formula in SQL, which could determine the top 3 countries in usage.
If you run the following code (Microsoft SQL Server) that setups the example,
CREATE TABLE #TempTable ( FieldType nvarchar(20), FieldUsage nvarchar(500) );INSERT INTO #TempTable (FieldType, FieldUsage)VALUES ('Field1', 'DE Germany 18799 /US USA 11913 /BR Brazil 9840 /CN China 9436 /IN India 9113 /GB United Kingdom 8910'), ('Field2', 'CN China 446 /DE Germany 102'), ('Field3', 'PL Poland 91 /US USA 44 /FR France 43 /IN India 22 /AR Argentina 17 /SE Sweden 16')SELECT * FROM #TempTable/*DELETE FROM #TempTableDROP TABLE #TempTable*/
which is so in column FieldUsage a pattern of Country code + Country name + CountOfFields used with separating '/' character (if you look on the numbers they are decreasing, so the top usage is the first country, then comes the second etc.):
The target would be an SQL formula which results as it is shown on the below picture in column C, briefly cutting the LEFT three countries from column B (if there are just two countries, then two) to determine the top 3 usage:
Could you please advise which SQL formula could result that?
In the real environment the FieldUsage field can be very long, sometimes listing 30-40 countries (meaning that 30-40 times being the '/' separator there)
Thanks in advance,
SQLMicrosoft SQL Server
Last Comment
csehz
8/22/2022 - Mon
slightwv (䄆 Netminder)
Your SQL Server version would help a lot.
As long as FieldUsage is ALWAYS in the order you want, what you are really after is to 'trim' everything after the third '/'.
This works on the data provided. It uses a recursive CTE. As long as there isn't a LOT of rows, it should be fine. They don't scale well.
with recursive_cte as ( select 1 as row, FieldType, charindex('/', FieldUsage) offset, FieldUsage + '/' FieldUsage from #TempTable union all select row + 1, FieldType, charindex('/', FieldUsage, offset + 1), FieldUsage from recursive_cte where offset>0 and row<=2),get_correct_row as ( select row, FieldType, FieldUsage, offset, row_number() over(partition by FieldType order by offset desc) rn from recursive_cte )select FieldType, FieldUsage, SubString(FieldUsage,1,offset-1) Top3Countriesfrom get_correct_rowwhere rn=1
CHARINDEX() will return the position of a string within a string. Using a combination of those will locate the third position of '/' and then LEFT() can be used to output that portion of the source string.
I have used an OUTER APPLY just in case there are missing strings and a case expression if there isn't a value returned by the apply.
select FieldType , FieldUsage , case when oa.pos > 0 then left(FieldUsage,oa.pos-1) end as Top3Countriesfrom #TempTableouter apply (select charindex('/',FieldUsage,charindex('/',FieldUsage,charindex('/',FieldUsage,1)+1)+1) as pos ) oa
As long as FieldUsage is ALWAYS in the order you want, what you are really after is to 'trim' everything after the third '/'.
This works on the data provided. It uses a recursive CTE. As long as there isn't a LOT of rows, it should be fine. They don't scale well.
Open in new window
My test Fiddle is here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7e429c26d7c97497a9e736c9ac3391b1