xxxxxxxxxx
CREATE FUNCTION [dbo].[Func_SplitString]
( @Values VARCHAR(MAX)
, @splitStr VARCHAR(50)
)
RETURNS @Result_Table TABLE
(
[value] nvarchar(MAX) NULL
)
BEGIN
DECLARE @TempStr nvarchar(MAX)
WHILE (CHARINDEX(@splitStr,@Values)>0)
BEGIN
SET @TempStr=SUBSTRING(@Values,1,CHARINDEX(@splitStr,@Values)-1)
INSERT INTO @Result_Table (value) VALUES (ltrim(rtrim(isnull(@TempStr, ''))))
SET @Values = REPLACE(@Values,@TempStr+@splitStr,'')
END/*End While*/
IF(LEN(RTRIM(LTRIM(@Values)))>0 AND CHARINDEX(@splitStr,RTRIM(LTRIM(@Values)))=0)
BEGIN
SET @TempStr=@Values
INSERT INTO @Result_Table (value) VALUES (ltrim(rtrim(isnull(@TempStr, ''))))
End /*End IF*/
RETURN
END
xxxxxxxxxx
-- Assume you have the CTE
WITH cte AS (
SELECT STRING_AGG(Country, ', ') AS ConcatenatedCountries
FROM Country_Medals
)
SELECT value AS Country
FROM cte
CROSS APPLY STRING_SPLIT(cte.ConcatenatedCountries, ', '); -- Use STRING_SPLIT with CROSS APPLY
-- Split into words
SELECT
CASE
WHEN category LIKE '%: %' THEN SPLIT_PART(category, ': ', 1)
WHEN category LIKE '% - %' THEN SPLIT_PART(category, ' - ', 1)
ELSE SPLIT_PART(category, ' | ', 1)
END AS major_category, -- alias the result
sum(businesses) -- also select number of businesses
FROM naics
GROUP BY major_category;