I have a table that contains a field called RegionID. Values are stored in the field as comma-delimited string of numbers. It's an nvarchar.
A sample record might look like this:
Or simply this:
I'm running a count then grouping them by the RegionID but the records with more than one value are shewing the results.
I have a query like this:
SELECT RegionID, COUNT(RegionID) AS Total FROM Regions GROUP BY RegionID
My results are this:
I preferrably want to have all the RegionID's counted individually. I have 6 total RegionIDs so I really want the results above to be this:
Each comma-delimited value is counted individually.
I hope I have somewhat explained what I need. Any help will be greatly appreciated!