1

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

2
  • Hi and welcome to the forum. Please tag your SQL-Server version.
    – McNets
    Commented May 4, 2020 at 7:15
  • Hi and welcome to the forum! Maybe you could consider changing your schema?
    – Vérace
    Commented May 4, 2020 at 8:47

2 Answers 2

2

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
1

First create a splitting function that will get string and delimiter and will return a table.

CREATE FUNCTION [dbo].[fn_Split] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(10))
RETURNS @OutputTable TABLE ( [String] VARCHAR(8000))
AS
BEGIN
    SET @Delimiter = LTRIM(RTRIM(@Delimiter))
    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END

Then get the expected result using CROSS APPLY as follows.

CREATE TABLE #reviewer(
Reviewer VARCHAR(100)
)
GO

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

SELECT spl.String, COUNT(*)
FROM #reviewer
CROSS APPLY dbo.fn_Split(Reviewer, ',') spl
GROUP BY spl.String
4
  • If OP is running SQL-Server 2016 or above maybe it is more suitable to use the integrated STRING_SPLIT function.
    – McNets
    Commented May 4, 2020 at 7:53
  • Thank you @Artashes. I am very new to SQL. Could you please help me with this step by step. I want to add this to MS Access so where do I paste this code? to VBA or SQL Query? Also, the combination of Persons will be different and number of Persons will be different. So will the second part work?
    – Biraj Deb
    Commented May 4, 2020 at 9:29
  • @BirajDeb The provided solution is for MS SQL Server as you mentioned in tag of your question. Commented May 4, 2020 at 15:03
  • Thank you. I thought I could get a solution for MS Access. Thanks for your support
    – Biraj Deb
    Commented May 4, 2020 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.