Sql Server: Group Query Based On Substring Of Column

I need to get a substring from a column 'source' from typeTable and be able to get stats on each region from that one column. A row would look something like 'server001[en-US]'. Th

Solution 1:

You already had the code. GROUP BY the CASE statement you used to get country:

GROUPBYcasewhen (charindex('[', typeTable.source) >0and charindex(']', typeTable.source) >0)
                    thensubstring(typeTable.source, charindex('[', typeTable.source) +1, (charindex(']', typeTable.source) -1) - charindex('[', typeTable.source))
                    elsenullend

And by the way, your code will not do what your comments say you want it to do:

--Print report:
PRINT 'countForType: '+CAST(@countForTypeASVARCHAR);
PRINT 'totalForType: '+CAST(@totalForTypeASVARCHAR);
--for each country, print the amounts/ percentages etc...
PRINT 'country: '+CAST (@countryASVARCHAR);

You are storing data in scalar variables, which means the execution of your query will only store one value into each of the variables. It will not allow you to loop through each result. To get the output in the exact format you specified in your question, you will need to use either a WHILE loop or a CURSOR.

However, I question whether you really need SQL to output that format. It would be better to let SQL return a result set, and format the output in your front end application.