The whole query is quite complex and embeds a lot of data that is retrieved from 5 different tables with different references. I'm almost there and now it's just a CASE command that not does what I want.
The problem is in the "Area_Numbers" column. The entries that have no value get parentheses instead of "NULL"
Area_Numbers column
The line that makes this column is like this, and it is the "CONCAT" command that creates the unwanted parantheses:
CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')') AS Area_Numbers
I then try adding another CASE condition at the beginning to only include the poses that have a value
CASE WHEN UCD.CustomFieldID LIKE '1' THEN (CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')')) ELSE NULL END AS Area_Numbers
It then gives the following error message:
Msg 8120, Level 16, State 1, Line 4
Column 'UserCustomFieldGroupData.CustomFieldID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I then try to add the variable "max" at the beginning, as it solved similar errors I had before and you don't need to add the column in GROUP BY
max(CASE WHEN UCD.CustomFieldID LIKE '1' THEN (CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')')) ELSE NULL END) AS Area_Numbers
It instead gives this error message:
Msg 130, Level 15, State 1, Line 5
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I then go into another strategy and move the "CONCAT" part to after "THEN" in the string to only activate it on the lines where the CASE question is correct:
CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN CONCAT('(',(STRING_AGG((SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1))), ')(')), ')') ELSE NULL END AS Area_Numbers
This results in all the sources in the row needing to be included in GROUP BY:
Msg 8120, Level 16, State 1, Line 6
Column 'AccessLevels.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'AccessLevels.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'UserCustomFieldGroupData.CustomFieldID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
If I complete in this mode with MAX at the beginning, the error message that it is not possible to use an aggregation function on an already aggregated value comes up
max(CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN CONCAT('(',(STRING_AGG((SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1))), ')(')), ')') ELSE NULL END) AS Area_Numbers
Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.