Ms-access – Split a string into multiple rows and get the count for each of them

ms access

I have a table "Production Database" in Server. I am using MS Access as a front end interface. I have some forms there. I want to create a query which will split records in "Reviewer" column and give me count for each of them. below is an example of the column, how it looks like

Reviewer
-----------------------
Person1,Person2,Person5 
Person3,Person1
Person4

Please help. Thanks in advance

Best Answer

You could try something like this if you have a recent version of SQL Server! I've used PostgreSQL for this example - but SQL Server has the STRING_SPLIT function which appears to be very similar to PostgreSQL's regexp_split_to_table function.

I can't appear to get SQL Server's function to work on dbfiddle.uk (Express Edition issue?). My own dbfiddle of the code below can be found here! There's also an SQL Server UNNEST function but the second SQL doesn't appear to be as compatible!

Create and populate your table -

CREATE TABLE reviewer(
Reviewer TEXT
);

data -

INSERT INTO reviewer
        ( Reviewer )
VALUES ('Person1,Person2,Person5') 
,('Person3,Person1')
,('Person4');

and then run this SQL (suitably translated for SQL Server) -

SELECT rev, COUNT(rev) 
FROM
(
  SELECT regexp_split_to_table(r.reviewer, ',') AS rev -- use STRING_SPLIT here!
  FROM reviewer r
) AS tab
GROUP BY rev
ORDER BY rev

Result -

Rev     count
Person1     2
Person2     1
Person3     1
Person4     1
Person5     1

You should always include your version of SQL Server when asking questions here. p.s. welcome to the forum!

You could also try something like this - but it would appear to be more difficult to translate the SQL - just an FYI - same result!

SELECT rev, COUNT(rev)
FROM
(
  SELECT rev
  FROM reviewer r,
  unnest(string_to_array(r.reviewer, ',')) AS rev
) AS tab
GROUP BY rev
ORDER BY rev
Related Question